cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
cancel
Showing results for 
Search instead for 
Did you mean: 

Is timestamp difference always INTERVAL DAY TO SECOND?

vr
Contributor

My observations show that timestamp difference has type of INTERVAL DAY TO SECONDS:

select typeof(getdate() - current_date())
-----------------------------------------
interval day to second

But is it guaranteed? Can it be DAY TO MINUTE or, say, YEAR TO MONTH depending on the input? I could not find that in the documentation.

1 ACCEPTED SOLUTION

Accepted Solutions

sher
Valued Contributor II
3 REPLIES 3

Hubert-Dudek
Esteemed Contributor III

It will be like that if you manipulate seconds. As getdate() includes seconds, it will be that object, but if you make select typeof(getdate() + INTERVAL 1 DAY), it will be a timestamp.

Maybe having seconds as integer guaranteed from midnight will be the best:

select CAST(current_timestamp() AS INT) - CAST(timestamp(current_date()) AS INT)

AdrianLobacz
Contributor

Some hints for you

TIMESTAMP supports fractional seconds, unlike DATE which supports only seconds

TIMESTAMP exist in three flavors:

TIMESTAMP does not contain any time zone information.

TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE contain time zone information

Regarding calculation and manipulation there is actually no difference between TIMESTAMP and DATE. There are only a very few functions which support only either of these two types.

DATE is an old data type. TIMESTAMP was introduced later (well "later" means in 9i, i.e. 20 years ago) INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND are interval data types, they do not contain any absolute date information.

Hope this gave some hints.

sher
Valued Contributor II

you can check here for given example: https://docs.databricks.com/sql/language-manual/functions/minussign.html

this might help to you.

Welcome to Databricks Community: Lets learn, network and celebrate together

Join our fast-growing data practitioner and expert community of 80K+ members, ready to discover, help and collaborate together while making meaningful connections. 

Click here to register and join today! 

Engage in exciting technical discussions, join a group with your peers and meet our Featured Members.