topic Is timestamp difference always INTERVAL DAY TO SECOND? in Data Engineering

Is timestamp difference always INTERVAL DAY TO SECOND?

vr — Thu, 05 Jan 2023 01:37:03 GMT

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.

Re: Is timestamp difference always INTERVAL DAY TO SECOND?

Hubert-Dudek — Thu, 05 Jan 2023 09:26:00 GMT

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)

Re: Is timestamp difference always INTERVAL DAY TO SECOND?

AdrianLobacz — Thu, 05 Jan 2023 12:25:48 GMT

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.

Re: Is timestamp difference always INTERVAL DAY TO SECOND?

sher — Fri, 06 Jan 2023 06:20:02 GMT

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

this might help to you.