Data Types for Dates, Timestamps and Intervals¶
Virtual DataPort has data types to hold datetime and interval values. See the table below:
Name of the Type in Denodo |
Description |
Name of the Type in the SQL Standard |
|---|---|---|
LOCALDATE |
Date without a time zone (year, month and day) |
DATE |
TIME |
Time without a time zone (hour, minute, second and fraction of second with nanosecond precision) |
TIME WITHOUT TIME ZONE |
TIMESTAMP |
Timestamp without a time zone (year, month, day, hour, minute, second and fraction of second with nanosecond precision) |
TIMESTAMP WITHOUT TIME ZONE |
TIMESTAMPTZ |
Timestamp with a time zone displacement |
TIMESTAMP WITH TIME ZONE |
INTERVALDAYSECOND |
Duration of a period of time with a precision that can include any set of contiguous fields other than YEAR or MONTH |
day-time intervals |
INTERVALYEARMONTH |
Duration of a period of time with a precision that includes a YEAR field or a MONTH field, or both |
year-month intervals |
DATE (deprecated) |
Timestamp with a time zone displacement. Although similar to |
N/A |
Virtual DataPort maintains the DATE type from previous versions to keep backward compatibility. However, it will be removed in future major versions so you should plan on stop using it on your new developments. This type is similar to the type TIMESTAMP WITH LOCAL TIMEZONE of Oracle. This data type has been deprecated because it introduces great complexities when dealing with this time, particularly when the client applications or the data sources run on a different time zone than the Denodo server.
Note
For more information about date patterns format, refer to Date and Time Pattern Strings. It is recommended to use date type literals instead of text literals for dates, as using invalid patterns with text literals could lead to unexpected results.
Rules to Build Queries that Deal with Datetime Types¶
The following subsections list the rules you should follow when executing queries that involve datetime fields:
Most Appropriate Method to Define a Datetime Value¶
To build a datetime literal you have two options:
Use the syntax of the standard (name of the type followed by the value on a specific format).
Or with a function.
Both methods are equivalent.
Type in Denodo |
Option to Build a Value of this Type |
Examples |
|---|---|---|
localdate |
Literal |
DATE '2018-01-15'
|
Function |
to_localdate('yyyy-MM-dd', '2018-01-15')
|
|
time |
Literal |
TIME '21:45:59'
TIME '21:45:59.999'
|
Function |
to_time('HH:mm:ss.SSS','21:45:59.999')
|
|
timestamp |
Literal |
TIMESTAMP '2018-01-15 21:45:01'
TIMESTAMP '2018-01-15 21:45:01.256'
|
Function |
to_timestamp(
'yyyy-MM-dd HH:mm:ss.SSS'
, '2018-01-15 21:45:01.256')
|
|
date (deprecated) |
The same as with timestamptz (see below). From the perspective of clients, both types behave in the same way |
|
timestamptz |
Literal |
TIMESTAMP WITH TIME ZONE '2018-01-15 21:45:01 +02:00'
TIMESTAMP WITH TIME ZONE '2018-01-15 21:45:01.256
+02:00'
|
Function |
to_timestamptz(
'yyyy-MM-dd HH:mm:ss.SSS XXX'
, '2018-01-15 21:45:01.256 +02:00')
|
|
interval_year_month |
Literal |
INTERVAL '145' YEAR
INTERVAL '145-11' YEAR TO MONTH
|
Function |
There are no functions to build intervals |
|
time |
Literal |
INTERVAL '4 5:12:10.222' DAY TO SECOND
INTERVAL '4 5:12' DAY TO MINUTE
|
Function |
There are no functions to build intervals |
|
Use the Appropriate Function to Obtain the Current Datetime¶
To compare a datetime value with the current instant of time, use the appropriate function depending on the type you are comparing with.
Data Type |
Function to Use Obtain the Current Instant |
|---|---|
localdate |
CURRENT_DATE() or CURRENT_DATE |
time |
LOCALTIME |
timestamp |
LOCALTIMESTAMP or LOCALTIMESTAMP() |
timestamptz |
CURRENT_TIMESTAMP or NOW() |
date (deprecated) |
NOW() |
LOCALTIMESTAMP was introduced with 8.0u20200201. If you are using Denodo 8.0 GA (no updates), execute CAST(CURRENT_TIMESTAMP AS TIMESTAMP WITHOUT TIME ZONE) instead.
LOCALTIME was introduced with 8.0u20210715. If you are using Denodo 8.0 GA (no updates) or 8.0u20210209, execute CAST(CURRENT_TIMESTAMP AS TIME WITHOUT TIME ZONE) instead.
Converting a Datetime Value to Another Datetime Value¶
A datetime value of one type can be converted to a datetime value of another type. The table below shows the possible conversions between different datetime data types.
to LOCALDATE |
to TIME |
to TIMESTAMP |
to TIMESTAMPTZ |
|
|---|---|---|---|---|
from LOCALDATE |
trivial |
not supported |
Copy year, month, day. Set hour, minute, second to (zero) |
SV→TS w/o TZ→TS w/ TZ |
from TIME |
not supported |
trivial |
Copy date fields from CURRENT_DATE and time fields from SV |
SV→TS w/o TZ→TS w/ TZ |
from TIMESTAMP |
Copy date fields from SV |
Copy time fields from SV |
trivial |
TV.UTC = SV-STZD; TV.TZ=STZD |
to TIMESTAMPTZ |
SV → TS w/o TZ→LOCALDATE |
SV→TS w/o TZ→TIME |
SV.UTC + SV.TZ |
trivial |
SV: source value.
TV: target value.
UTC: UTC component of SV or TV (if and only if the source or target has time zone).
TZ: time zone displacement of SV or TV (if and only if the source or target has time zone), STZD is the SQL-session default time zone displacement.
“→”: the conversion is conceptually done in several steps. For example, from “timestamptz” to “localdate”, the conversion is “SV → TS w/o TZ→LOCALDATE”. This means that the “timestamptz” value is converted to “timestamp”, and the “timestamp” to “date”.
Rules:
All these conversions are done implicitly if necessary, except the conversions from and to timestamptz, which need to be done explicitly (using the function CAST).
When converting from timestamp to timestamptz, the time zone added to the timestamptz value is the time zone of the i18n of the Denodo server.
Examples:
2017-10-15 21:45:00 → 2017-10-15 21:45:00 +02:00
From timestamp to timestamptz. The time zone of the new value is the i18n of the Server.
2017-01-15 21:15:00 → 2017-01-15
From timestamp to date: remove the time components.
2017-10-15 21:45:01 → 21:45:01
From timestamp to time: remove the date components.
2017-10-15 21:45:00 +05:00 → 2017-10-15 18:45:00
From timestamptz to timestamp: convert to local timestamp subtracting the difference between the time zone displacement of the source value and the time zone of the i18n of the Denodo server. In this example, the time zone of the Server is +02:00.
2017-10-15 21:45:00 +05:00 → 2017-01-16
From timestamptz to localdate: first convert to timestamp (2017-10-16 00:45:00), and then remove the time part. In this example, the time zone of the Server is +08:00. Note that in this example, the day in the source value is different from the day in the target value. That is because in the first stage of the conversion (from timestamptz to timestamp), the time is modified according to the difference in time zones and this can lead to a change in the day as well.
2017-10-15 21:45:00 +05:00 → 18:45:00
From timestamptz to time: first convert to timestamp (2017-10-15 18:45:00), and then remove the date part. In this example, the time zone of the Server is +02:00.
2017-01-15 → 2017-01-15 00:00:00
From localdate to timestamp: set the time components to zero.
2017-01-15 → 2017-01-15 00:00:00 +05:00
From localdate to timestamptz: first convert to timestamp, and then add the time zone of the i18n of the Server. In this example, the time zone of the Server is +05:00.
21:45:01 → 2017-10-15 21:45:01
From time to timestamp: complete the date part with the current date in the Denodo server.
21:45:00 → 2017-10-15 21:45:00 +02:00
From time to timestamptz: first convert to timestamp (2017-10-15 21:45:00), and then add the time zone of the i18n of the Server. In this example, the time zone of the Server is +02:00.
Data Types for Intervals¶
Intervals represent a period of time. There are two types of intervals:
intervalyearmonth: duration of a period of time with a precision that includes a YEAR field or a MONTH field, or both. Examples:-- 145 years INTERVAL '145' YEAR -- 145 years and 11 months INTERVAL '145-11' YEAR TO MONTH -- 145 months INTERVAL '145' MONTH
intervaldaysecond: duration of a period of time with a precision that can include any set of contiguous fields other than YEAR or MONTH.-- 4 days, 5 hours, 12 minutes, 10 seconds and 222 milliseconds INTERVAL '4 5:12:10.222' DAY TO SECOND -- 4 days, 5 hours and 12 minutes INTERVAL '4 5:12' DAY TO MINUTE -- 11 hours and 20 minutes INTERVAL '11:20' HOUR TO MINUTE -- 10 minutes and 22 seconds INTERVAL '10:22' MINUTE TO SECOND -- 30 seconds and 123 milliseconds INTERVAL '30.123' SECOND
Two interval values are only comparable if they are of the same interval type.
Arithmetic Operators for Datetime and Interval Values¶
You can use arithmetic operators to perform operations over values of type datetime and INTERVAL.
Operand 1 |
Operator |
Operand 2 |
Result Type |
|---|---|---|---|
Datetime |
- |
Datetime |
long *-1 |
Datetime |
+ or - |
Interval |
Datetime |
Time |
+ or - |
Time |
long *-2 |
Interval |
+ |
Datetime |
Datetime |
Interval |
+ or - |
Interval |
Interval |
Interval |
* or / |
int or long |
Interval |
int or long |
* |
Interval |
Interval |
*-1: number of days between <operand 1> and <operand 2>. The result is a positive number if <operand 1> is a more recent datetime than <operand 2>. Otherwise, the result is a negative number.
*-2: milliseconds between <operand 1> and <operand 2>. The result is a positive number if <operand 1> is higher than <operand 2>. Otherwise, the result is a negative number.