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 TIMESTAMPTZ, it only has millisecond precision and its behavior is not 100% ANSI SQL-compliant. It should not be used anymore. Maintained for compatibility reasons. N/A

Virtual DataPort maintains the DATE type from previous versions to keep backward compatibility. However, it will be removed in future major versions. This type is similar to the type TIMESTAMP WITH LOCAL TIMEZONE of Oracle.

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:

  1. Use the syntax of the standard (name of the type followed by the value on a specific format).
  2. 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.

Type of the Expression you are Comparing With Function to Use Obtain the Current Instant
localdate CURRENT_DATE()
time CAST(CURRENT_TIMESTAMP AS TIME WITHOUT TIME ZONE)
timestamp CAST(CURRENT_TIMESTAMP AS TIMESTAMP WITHOUT TIME ZONE)
date (deprecated) NOW()
timestamptz NOW()

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.

Datetime data type conversions
  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:

  1. 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
    
  2. 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.

Valid operators involving datetimes and intervals
Operand 1 Operator Operand 2 Result Type
Datetime - Datetime long *-1
Datetime + or - Interval Datetime
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.

Nanosecond Precision of Time Values

Starting with Denodo 7.0 update 20180926, the types TIME, TIMESTAMP, TIMESTAMPTZ and INTERVALDAYSECOND have nanosecond precision, supporting up to nine digits as fraction of second.

In previous updates of Denodo 7.0, these values only had millisecond precision. If a source returned these values with a precision higher than millisecond, Denodo truncated them to milliseconds. With the update 20180926, these values are no longer truncated. If the change in precision affects other applications, you can go back to the previous behavior by running this command from the VQL Shell:

SET 'com.denodo.vdb.types.datetime.enableNanoSecondPrecisionSupport' = 'false';

Restart the Virtual DataPort server to apply the change.