Date / time functions - Amazon Timestream
General and conversion date / time functionsInterval and duration functionsFormatting and parsing functionsExtraction functions

Date / time functions

Note

Timestream for LiveAnalytics does not support negative time values. Any operation resulting in negative time results in error.

Timestream for LiveAnalytics uses UTC timezone for date and time. Timestream supports the following functions for date and time.

General and conversion

Timestream for LiveAnalytics supports the following general and conversion functions for date and time.

Function Output data type Description

current_date

date

Returns current date in UTC. No parentheses used.

SELECT current_date

Example result: 2022-07-07

Note

This is also a reserved keyword. For a list of reserved keywords, see Reserved keywords.

current_time

time

Returns current time in UTC. No parentheses used.

SELECT current_time

Example result: 17:41:52.827000000

Note

This is also a reserved keyword. For a list of reserved keywords, see Reserved keywords.

current_timestamp or now()

timestamp

Returns current timestamp in UTC.

SELECT current_timestamp

Example result: 2022-07-07 17:42:32.939000000

Note

This is also a reserved keyword. For a list of reserved keywords, see Reserved keywords.

current_timezone()

varchar

The value will be 'UTC.'

Timestream uses UTC timezone for date and time.

SELECT current_timezone()

Example result: UTC

date(varchar(x)), date(timestamp)

date

 
SELECT date(TIMESTAMP '2022-07-07 17:44:43.771000000')

Example result: 2022-07-07

last_day_of_month(timestamp), last_day_of_month(date)

date

 
SELECT last_day_of_month(TIMESTAMP '2022-07-07 17:44:43.771000000')

Example result: 2022-07-31

from_iso8601_timestamp(string)

timestamp

Parses the ISO 8601 timestamp into internal timestamp format.

SELECT from_iso8601_timestamp('2022-06-17T08:04:05.000000000+05:00')

Example result: 2022-06-17 03:04:05.000000000

from_iso8601_date(string)

date

Parses the ISO 8601 date string into internal timestamp format for UTC 00:00:00 of the specified date.

SELECT from_iso8601_date('2022-07-17')

Example result: 2022-07-17

to_iso8601(timestamp), to_iso8601(date)

varchar

Returns an ISO 8601 formatted string for the input.

SELECT to_iso8601(from_iso8601_date('2022-06-17'))

Example result: 2022-06-17

from_milliseconds(bigint)

timestamp

 
SELECT from_milliseconds(1)

Example result: 1970-01-01 00:00:00.001000000

from_nanoseconds(bigint)

timestamp

 
select from_nanoseconds(300000001)

Example result: 1970-01-01 00:00:00.300000001

from_unixtime(double)

timestamp

Returns a timestamp which corresponds to the provided unixtime.

SELECT from_unixtime(1)

Example result: 1970-01-01 00:00:01.000000000

localtime

time

Returns current time in UTC. No parentheses used.

SELECT localtime

Example result: 17:58:22.654000000

Note

This is also a reserved keyword. For a list of reserved keywords, see Reserved keywords.

localtimestamp

timestamp

Returns current timestamp in UTC. No parentheses used.

SELECT localtimestamp

Example result: 2022-07-07 17:59:04.368000000

Note

This is also a reserved keyword. For a list of reserved keywords, see Reserved keywords.

to_milliseconds(interval day to second), to_milliseconds(timestamp)

bigint

 
SELECT to_milliseconds(INTERVAL '2' DAY + INTERVAL '3' HOUR)

Example result: 183600000

SELECT to_milliseconds(TIMESTAMP '2022-06-17 17:44:43.771000000')

Example result: 1655487883771

to_nanoseconds(interval day to second), to_nanoseconds(timestamp)

bigint

 
SELECT to_nanoseconds(INTERVAL '2' DAY + INTERVAL '3' HOUR)

Example result: 183600000000000

SELECT to_nanoseconds(TIMESTAMP '2022-06-17 17:44:43.771000678')

Example result: 1655487883771000678

to_unixtime(timestamp)

double

Returns unixtime for the provided timestamp.

SELECT to_unixtime('2022-06-17 17:44:43.771000000')

Example result: 1.6554878837710001E9

date_trunc(unit, timestamp)

timestamp

Returns the timestamp truncated to unit, where unit is one of [second, minute, hour, day, week, month, quarter, or year].

SELECT date_trunc('minute', TIMESTAMP '2022-06-17 17:44:43.771000000')

Example result: 2022-06-17 17:44:00.000000000

Interval and duration

Timestream for LiveAnalytics supports the following interval and duration functions for date and time.

Function Output data type Description

date_add(unit, bigint, date), date_add(unit, bigint, time), date_add(varchar(x), bigint, timestamp)

timestamp

Adds a bigint of units, where unit is one of [second, minute, hour, day, week, month, quarter, or year].

SELECT date_add('hour', 9, TIMESTAMP '2022-06-17 00:00:00')

Example result: 2022-06-17 09:00:00.000000000

date_diff(unit, date, date) , date_diff(unit, time, time) , date_diff(unit, timestamp, timestamp)

bigint

Returns a difference, where unit is one of [second, minute, hour, day, week, month, quarter, or year].

SELECT date_diff('day', DATE '2020-03-01', DATE '2020-03-02')

Example result: 1

parse_duration(string)

interval

Parses the input string to return an interval equivalent.

SELECT parse_duration('42.8ms')

Example result: 0 00:00:00.042800000

SELECT typeof(parse_duration('42.8ms'))

Example result: interval day to second

bin(timestamp, interval)

timestamp

Rounds down the timestamp parameter's integer value to the nearest multiple of the interval parameter's integer value.

The meaning of this return value may not be obvious. It is calculated using integer arithmetic first by dividing the timestamp integer by the interval integer and then by multiplying the result by the interval integer.

Keeping in mind that a timestamp specifies a UTC point in time as a number of fractions of a second elapsed since the POSIX epoch (January 1, 1970), the return value will seldom align with calendar units. For example, if you specify an interval of 30 days, all the days since the epoch are divided into 30-day increments, and the start of the most recent 30-day increment is returned, which has no relationship to calendar months.

Here are some examples:

bin(TIMESTAMP '2022-06-17 10:15:20', 5m)     ==> 2022-06-17 10:15:00.000000000
bin(TIMESTAMP '2022-06-17 10:15:20', 1d)     ==> 2022-06-17 00:00:00.000000000
bin(TIMESTAMP '2022-06-17 10:15:20', 10day)  ==> 2022-06-17 00:00:00.000000000
bin(TIMESTAMP '2022-06-17 10:15:20', 30day)  ==> 2022-05-28 00:00:00.000000000

ago(interval)

timestamp

Returns the value corresponding to current_timestamp interval.

SELECT ago(1d)

Example result: 2022-07-06 21:08:53.245000000

interval literals such as 1h, 1d, and 30m

interval

Interval literals are a convenience for parse_duration(string). For example, 1d is the same as parse_duration('1d'). This allows the use of the literals wherever an interval is used. For example, ago(1d) and bin(<timestamp>, 1m).

Some interval literals act as shorthand for parse_duration. For example, parse_duration('1day'), 1day, parse_duration('1d'), and 1d each return 1 00:00:00.000000000 where the type is interval day to second. Space is allowed in the format provided to parse_duration. For example parse_duration('1day') also returns 00:00:00.000000000. But 1 day is not an interval literal.

The units related to interval day to second are ns, nanosecond, us, microsecond, ms, millisecond, s, second, m, minute, h, hour, d, and day.

There is also interval year to month. The units related to interval year to month are y, year, and month. For example, SELECT 1year returns 1-0. SELECT 12month also returns 1-0. SELECT 8month returns 0-8.

Although the unit of quarter is also available for some functions such as date_trunc and date_add, quarter is not available as part of an interval literal.

Formatting and parsing

Timestream for LiveAnalytics supports the following formatting and parsing functions for date and time.

Function Output data type Description

date_format(timestamp, varchar(x))

varchar

For more information about the format specifiers used by this function, see https://trino.io/docs/current/functions/datetime.html#mysql-date-functions

SELECT date_format(TIMESTAMP '2019-10-20 10:20:20', '%Y-%m-%d %H:%i:%s')

Example result: 2019-10-20 10:20:20

date_parse(varchar(x), varchar(y))

timestamp

For more information about the format specifiers used by this function, see https://trino.io/docs/current/functions/datetime.html#mysql-date-functions

SELECT date_parse('2019-10-20 10:20:20', '%Y-%m-%d %H:%i:%s')

Example result: 2019-10-20 10:20:20.000000000

format_datetime(timestamp, varchar(x))

varchar

For more information about the format string used by this function, see http://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html

SELECT format_datetime(parse_datetime('1968-01-13 12', 'yyyy-MM-dd HH'), 'yyyy-MM-dd HH')

Example result: 1968-01-13 12

parse_datetime(varchar(x), varchar(y))

timestamp

For more information about the format string used by this function, see http://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html

SELECT parse_datetime('2019-12-29 10:10 PST', 'uuuu-LL-dd HH:mm z')

Example result: 2019-12-29 18:10:00.000000000

Extraction

Timestream for LiveAnalytics supports the following extraction functions for date and time. The extract function is the basis for the remaining convenience functions.

Function Output data type Description

extract

bigint

Extracts a field from a timestamp, where field is one of [YEAR, QUARTER, MONTH, WEEK, DAY, DAY_OF_MONTH, DAY_OF_WEEK, DOW, DAY_OF_YEAR, DOY, YEAR_OF_WEEK, YOW, HOUR, MINUTE, or SECOND].

SELECT extract(YEAR FROM '2019-10-12 23:10:34.000000000')

Example result: 2019

day(timestamp), day(date), day(interval day to second)

bigint

 
SELECT day('2019-10-12 23:10:34.000000000')

Example result: 12

day_of_month(timestamp), day_of_month(date), day_of_month(interval day to second)

bigint

 
SELECT day_of_month('2019-10-12 23:10:34.000000000')

Example result: 12

day_of_week(timestamp), day_of_week(date)

bigint

 
SELECT day_of_week('2019-10-12 23:10:34.000000000')

Example result: 6

day_of_year(timestamp), day_of_year(date)

bigint

 
SELECT day_of_year('2019-10-12 23:10:34.000000000')

Example result: 285

dow(timestamp), dow(date)

bigint

Alias for day_of_week

doy(timestamp), doy(date)

bigint

Alias for day_of_year

hour(timestamp), hour(time), hour(interval day to second)

bigint

 
SELECT hour('2019-10-12 23:10:34.000000000')

Example result: 23

millisecond(timestamp), millisecond(time), millisecond(interval day to second)

bigint

 
SELECT millisecond('2019-10-12 23:10:34.000000000')

Example result: 0

minute(timestamp), minute(time), minute(interval day to second)

bigint

 
SELECT minute('2019-10-12 23:10:34.000000000')

Example result: 10

month(timestamp), month(date), month(interval year to month)

bigint

 
SELECT month('2019-10-12 23:10:34.000000000')

Example result: 10

nanosecond(timestamp), nanosecond(time), nanosecond(interval day to second)

bigint

 
SELECT nanosecond(current_timestamp)

Example result: 162000000

quarter(timestamp), quarter(date)

bigint

 
SELECT quarter('2019-10-12 23:10:34.000000000')

Example result: 4

second(timestamp), second(time), second(interval day to second)

bigint

 
SELECT second('2019-10-12 23:10:34.000000000')

Example result: 34

week(timestamp), week(date)

bigint

 
SELECT week('2019-10-12 23:10:34.000000000')

Example result: 41

week_of_year(timestamp), week_of_year(date)

bigint

Alias for week

year(timestamp), year(date), year(interval year to month)

bigint

 
SELECT year('2019-10-12 23:10:34.000000000')

Example result: 2019

year_of_week(timestamp), year_of_week(date)

bigint

 
SELECT year_of_week('2019-10-12 23:10:34.000000000')

Example result: 2019

yow(timestamp), yow(date)

bigint

Alias for year_of_week