cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
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.

Join 100K+ Data Experts: Register Now & Grow with Us!

Excited to expand your horizons with us? Click here to Register and begin your journey to success!

Already a member? Login and join your local regional user group! If there isn’t one near you, fill out this form and we’ll create one for you to join!