Date and time functions

Date and time functions return or operate on dates, times, or timestamps.

Date and time functions use the convention established within the java.text.SimpleDateFormat class to define the formats you can use with these functions. You can learn more about how this class defines formats by visiting the Javadocs for SimpleDateFormat.

Function	Definition	Datatype constraint
CURDATE() CURRENT_DATE[()]	Return current date - will return the same value for all invocations in the user command.	returns date.
CURTIME()	Return current time - will return the same value for all invocations in the user command. See also CURRENT_TIME.	returns time
NOW()	Return current timestamp (date and time with millisecond precision) - will return the same value for all invocations in the user command or procedure instruction. See also CURRENT_TIMESTAMP.	returns timestamp
CURRENT_TIME[(precision)]	Return current time - will return the same value for all invocations in the user command. The Teiid time type does not track fractional seconds, so the precision argument is effectively ignored. Without a precision is the same as CURTIME().	returns time
CURRENT_TIMESTAMP[(precision)]	Return current timestamp (date and time with millisecond precision) - will return the same value for all invocations with the same precision in the user command or procedure instruction. Without a precision is the same as NOW(). Since the current timestamp has only millisecond precision by default setting the precision to greater than 3 will have no effect.	returns timestamp
DAYNAME(x)	Return name of day in the default locale	x in {date, timestamp}, returns string
DAYOFMONTH(x)	Return day of month	x in {date, timestamp}, returns integer
DAYOFWEEK(x)	Return day of week (Sunday=1, Saturday=7)	x in {date, timestamp}, returns integer
DAYOFYEAR(x)	Return day number in year	x in {date, timestamp}, returns integer
EPOCH(x)	Return seconds since the unix epoch with microsecond precision	x in {date, timestamp}, returns double
EXTRACT(FIELD FROM x)	Return the given FIELD value from the date value x. Valid fields are YEAR, MONTH, DAYOFMONTH, HOUR, MINUTE, SECOND, QUARTER, EPOCH, DAYOFWEEK, and DAYOFYEAR. The extract behaves exactly like the function call FIELD(x) - and will return same results as the respective function. The SQL specification also allows for TIMEZONE_HOUR and TIMEZONE_MINUTE as extraction targets, but those are not currently implemented. In Teiid all date values are in the timezone of the server.	x in {date, time, timestamp}, epoch returns double, the others return integer
FORMATDATE(x, y)	Format date x using format y.	x is date, y is string, returns string
FORMATTIME(x, y)	Format time x using format y.	x is time, y is string, returns string
FORMATTIMESTAMP(x, y)	Format timestamp x using format y.	x is timestamp, y is string, returns string
FROM_MILLIS (millis)	Return the Timestamp value for the given milliseconds.	long UTC timestamp in milliseconds
FROM_UNIXTIME (unix_timestamp)	Return the Unix timestamp as a String value with the default format of yyyy/mm/dd hh:mm:ss.	long Unix timestamp (in seconds)
HOUR(x)	Return hour (in military 24-hour format).	x in {time, timestamp}, returns integer
MINUTE(x)	Return minute.	x in {time, timestamp}, returns integer
MODIFYTIMEZONE (timestamp, startTimeZone, endTimeZone)	Returns a timestamp based upon the incoming timestamp adjusted for the differential between the start and end time zones. If the server is in GMT-6, then modifytimezone({ts '2006-01-10 04:00:00.0'},'GMT-7', 'GMT-8') will return the timestamp {ts '2006-01-10 05:00:00.0'} as read in GMT-6. The value has been adjusted 1 hour ahead to compensate for the difference between GMT-7 and GMT-8.	startTimeZone and endTimeZone are strings, returns a timestamp
MODIFYTIMEZONE (timestamp, endTimeZone)	Return a timestamp in the same manner as modifytimezone(timestamp, startTimeZone, endTimeZone), but will assume that the startTimeZone is the same as the server process.	Timestamp is a timestamp; endTimeZone is a string, returns a timestamp
MONTH(x)	Return month.	x in {date, timestamp}, returns integer
MONTHNAME(x)	Return name of month in the default locale.	x in {date, timestamp}, returns string
PARSEDATE(x, y)	Parse date from x using format y.	x, y in {string}, returns date
PARSETIME(x, y)	Parse time from x using format y.	x, y in {string}, returns time
PARSETIMESTAMP(x,y)	Parse timestamp from x using format y.	x, y in {string}, returns timestamp
QUARTER(x)	Return quarter.	x in {date, timestamp}, returns integer
SECOND(x)	Return seconds.	x in {time, timestamp}, returns integer
TIMESTAMPCREATE(date, time)	Create a timestamp from a date and time.	date in {date}, time in {time}, returns timestamp
TO_MILLIS (timestamp)	Return the UTC timestamp in milliseconds.	timestamp value
UNIX_TIMESTAMP (unix_timestamp)	Return the long Unix timestamp (in seconds).	unix_timestamp String in the default format of yyyy/mm/dd hh:mm:ss
WEEK(x)	Return week in year 1-53. For customization information, see System Properties in the Administrator’s Guide.	x in {date, timestamp}, returns integer
YEAR(x)	Return four-digit year	x in {date, timestamp}, returns integer

Function

Definition

Datatype constraint

CURDATE()
CURRENT_DATE[()]

Return current date - will return the same value for all invocations in the user command.

returns date.

CURTIME()

Return current time - will return the same value for all invocations in the user command. See also CURRENT_TIME.

returns time

NOW()

Return current timestamp (date and time with millisecond precision) - will return the same value for all invocations in the user command or procedure instruction. See also CURRENT_TIMESTAMP.

returns timestamp

CURRENT_TIME[(precision)]

Return current time - will return the same value for all invocations in the user command. The Teiid time type does not track fractional seconds, so the precision argument is effectively ignored. Without a precision is the same as CURTIME().

returns time

CURRENT_TIMESTAMP[(precision)]

Return current timestamp (date and time with millisecond precision) - will return the same value for all invocations with the same precision in the user command or procedure instruction. Without a precision is the same as NOW(). Since the current timestamp has only millisecond precision by default setting the precision to greater than 3 will have no effect.

returns timestamp

DAYNAME(x)

Return name of day in the default locale

x in {date, timestamp}, returns string

DAYOFMONTH(x)

Return day of month

x in {date, timestamp}, returns integer

DAYOFWEEK(x)

Return day of week (Sunday=1, Saturday=7)

x in {date, timestamp}, returns integer

DAYOFYEAR(x)

Return day number in year

x in {date, timestamp}, returns integer

EPOCH(x)

Return seconds since the unix epoch with microsecond precision

x in {date, timestamp}, returns double

EXTRACT(FIELD FROM x)

Return the given FIELD value from the date value x. Valid fields are YEAR, MONTH, DAYOFMONTH, HOUR, MINUTE, SECOND, QUARTER, EPOCH, DAYOFWEEK, and DAYOFYEAR. The extract behaves exactly like the function call FIELD(x) - and will return same results as the respective function. The SQL specification also allows for TIMEZONE_HOUR and TIMEZONE_MINUTE as extraction targets, but those are not currently implemented. In Teiid all date values are in the timezone of the server.

x in {date, time, timestamp}, epoch returns double, the others return integer

FORMATDATE(x, y)

Format date x using format y.

x is date, y is string, returns string

FORMATTIME(x, y)

Format time x using format y.

x is time, y is string, returns string

FORMATTIMESTAMP(x, y)

Format timestamp x using format y.

x is timestamp, y is string, returns string

FROM_MILLIS (millis)

Return the Timestamp value for the given milliseconds.

long UTC timestamp in milliseconds

FROM_UNIXTIME (unix_timestamp)

Return the Unix timestamp as a String value with the default format of yyyy/mm/dd hh:mm:ss.

long Unix timestamp (in seconds)

HOUR(x)

Return hour (in military 24-hour format).

x in {time, timestamp}, returns integer

MINUTE(x)

Return minute.

x in {time, timestamp}, returns integer

MODIFYTIMEZONE (timestamp, startTimeZone, endTimeZone)

Returns a timestamp based upon the incoming timestamp adjusted for the differential between the start and end time zones. If the server is in GMT-6, then modifytimezone({ts '2006-01-10 04:00:00.0'},'GMT-7', 'GMT-8') will return the timestamp {ts '2006-01-10 05:00:00.0'} as read in GMT-6. The value has been adjusted 1 hour ahead to compensate for the difference between GMT-7 and GMT-8.

startTimeZone and endTimeZone are strings, returns a timestamp

MODIFYTIMEZONE (timestamp, endTimeZone)

Return a timestamp in the same manner as modifytimezone(timestamp, startTimeZone, endTimeZone), but will assume that the startTimeZone is the same as the server process.

Timestamp is a timestamp; endTimeZone is a string, returns a timestamp

MONTH(x)

Return month.

x in {date, timestamp}, returns integer

MONTHNAME(x)

Return name of month in the default locale.

x in {date, timestamp}, returns string

PARSEDATE(x, y)

Parse date from x using format y.

x, y in {string}, returns date

PARSETIME(x, y)

Parse time from x using format y.

x, y in {string}, returns time

PARSETIMESTAMP(x,y)

Parse timestamp from x using format y.

x, y in {string}, returns timestamp

QUARTER(x)

Return quarter.

x in {date, timestamp}, returns integer

SECOND(x)

Return seconds.

x in {time, timestamp}, returns integer

TIMESTAMPCREATE(date, time)

Create a timestamp from a date and time.

date in {date}, time in {time}, returns timestamp

TO_MILLIS (timestamp)

Return the UTC timestamp in milliseconds.

timestamp value

UNIX_TIMESTAMP (unix_timestamp)

Return the long Unix timestamp (in seconds).

unix_timestamp String in the default format of yyyy/mm/dd hh:mm:ss

WEEK(x)

Return week in year 1-53. For customization information, see System Properties in the Administrator’s Guide.

x in {date, timestamp}, returns integer

YEAR(x)

Return four-digit year

x in {date, timestamp}, returns integer

Timestampadd

Add a specified interval amount to the timestamp.

Syntax

TIMESTAMPADD(interval, count, timestamp)

Arguments

Name	Description
interval	A datetime interval unit, can be one of the following keywords: SQL_TSI_FRAC_SECOND - fractional seconds (billionths of a second) SQL_TSI_SECOND - seconds SQL_TSI_MINUTE - minutes SQL_TSI_HOUR - hours SQL_TSI_DAY - days SQL_TSI_WEEK - weeks using Sunday as the first day SQL_TSI_MONTH - months SQL_TSI_QUARTER - quarters (3 months) where the first quarter is months 1-3, etc. SQL_TSI_YEAR - years
count	A long or integer count of units to add to the timestamp. Negative values will subtract that number of units. Long values are allowed for symmetry with TIMESTAMPDIFF - but the effective range is still limited to integer values.
timestamp	A datetime expression.

Name

Description

interval

A datetime interval unit, can be one of the following keywords:

SQL_TSI_FRAC_SECOND - fractional seconds (billionths of a second)
SQL_TSI_SECOND - seconds
SQL_TSI_MINUTE - minutes
SQL_TSI_HOUR - hours
SQL_TSI_DAY - days
SQL_TSI_WEEK - weeks using Sunday as the first day
SQL_TSI_MONTH - months
SQL_TSI_QUARTER - quarters (3 months) where the first quarter is months 1-3, etc.
SQL_TSI_YEAR - years

count

A long or integer count of units to add to the timestamp. Negative values will subtract that number of units. Long values are allowed for symmetry with TIMESTAMPDIFF - but the effective range is still limited to integer values.

timestamp

A datetime expression.

Example

SELECT TIMESTAMPADD(SQL_TSI_MONTH, 12,'2016-10-10')
SELECT TIMESTAMPADD(SQL_TSI_SECOND, 12,'2016-10-10 23:59:59')

Timestampdiff

Calculates the number of date part intervals crossed between the two timestamps return a long value.

Syntax

TIMESTAMPDIFF(interval, startTime, endTime)

Arguments

Name	Description
interval	A datetime interval unit, the same as keywords used by Timestampadd.
startTime	A datetime expression.
endTime	A datetime expression.

Name

Description

interval

A datetime interval unit, the same as keywords used by Timestampadd.

startTime

A datetime expression.

endTime

A datetime expression.

Example

SELECT TIMESTAMPDIFF(SQL_TSI_MONTH,'2000-01-02','2016-10-10')
SELECT TIMESTAMPDIFF(SQL_TSI_SECOND,'2000-01-02 00:00:00','2016-10-10 23:59:59')
SELECT TIMESTAMPDIFF(SQL_TSI_FRAC_SECOND,'2000-01-02 00:00:00.0','2016-10-10 23:59:59.999999')

Note

If (endTime > startTime), a non-negative number will be returned. If (endTime < startTime), a non-positive number will be returned. The date part difference difference is counted regardless of how close the timestamps are. For example, '2000-01-02 00:00:00.0' is still considered 1 hour ahead of '2000-01-01 23:59:59.999999'.

Compatibility issues

In SQL, Timestampdiff typically returns an integer. However the Teiid implementation returns a long. You might receive an exception if you expect a value out of the integer range from a pushed down timestampdiff.
The implementation of timestamp diff in Teiid 8.2 and earlier versions returned values based on the number of whole canonical interval approximations (365 days in a year, 91 days in a quarter, 30 days in a month, etc.) crossed. For example the difference in months between 2013-03-24 and 2013-04-01 was 0, but based upon the date parts crossed is 1. For information about backwards compatibility, see System Properties in the Adminstrator’s Guide.

Parsing date datatypes from strings

Teiid does not implicitly convert strings that contain dates presented in different formats, such as '19970101' and '31/1/1996' to date-related datatypes. You can, however, use the parseDate, parseTime, and parseTimestamp functions, described in the next section, to explicitly convert strings with a different format to the appropriate datatype. These functions use the convention established within the java.text.SimpleDateFormat class to define the formats you can use with these functions. For more information about how this class defines date and time string formats, see Javadocs for SimpleDateFormat. Note that the format strings are specific to your Java default locale.

For example, you could use these function calls, with the formatting string that adheres to the java.text.SimpleDateFormat convention, to parse strings and return the datatype you need:

String	Function call to parse string
'1997010'	parseDate(myDateString, 'yyyyMMdd')
'31/1/1996'	parseDate(myDateString, 'dd''/''MM''/''yyyy')
'22:08:56 CST'	parseTime (myTime, 'HH:mm:ss z')
'03.24.2003 at 06:14:32'	parseTimestamp(myTimestamp, 'MM.dd.yyyy''at''hh:mm:ss')

String

Function call to parse string

'1997010'

parseDate(myDateString, 'yyyyMMdd')

'31/1/1996'

parseDate(myDateString, 'dd''/''MM''/''yyyy')

'22:08:56 CST'

parseTime (myTime, 'HH:mm:ss z')

'03.24.2003 at 06:14:32'

parseTimestamp(myTimestamp, 'MM.dd.yyyy''at''hh:mm:ss')

Specifying time zones

Time zones can be specified in several formats. Common abbreviations such as EST for "Eastern standard time" are allowed but discouraged, as they can be ambiguous. Unambiguous time zones are defined in the form continent or ocean/largest city. For example, America/New_York, America/Buenos_Aires, or Europe/London. sAdditionally, you can specify a custom time zone by GMT offset: GMT[+/-]HH:MM.

For example: GMT-05:00

Date_Time functions

Date and time functions

results matching ""

No results matching ""