- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
01-04-2023 05:37 PM
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.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
01-05-2023 10:20 PM
you can check here for given example: https://docs.databricks.com/sql/language-manual/functions/minussign.html
this might help to you.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
01-05-2023 01:26 AM
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)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
01-05-2023 04:25 AM
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
01-05-2023 10:20 PM
you can check here for given example: https://docs.databricks.com/sql/language-manual/functions/minussign.html
this might help to you.
![](/skins/images/582998B45490C7019731A5B3A872C751/responsive_peak/images/icon_anonymous_message.png)
![](/skins/images/582998B45490C7019731A5B3A872C751/responsive_peak/images/icon_anonymous_message.png)