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 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
Use the Appropriate Function to Obtain the Current Datetime
Converting a Datetime Value to Another Datetime Value
Data Types for Intervals
Arithmetic Operators for Datetime and Interval Values

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'
localdate	Function	to_localdate('yyyy-MM-dd', '2018-01-15')
time	Literal	TIME '21:45:59' TIME '21:45:59.999'
time	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'
timestamp	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'
timestamptz	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
interval_year_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
time	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.

Function to obtain the current datetime¶
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()

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:

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.

Valid operators involving datetimes and intervals¶
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.

USER MANUALS