Date / time functions - Amazon Timestream

Date / time functions

Note

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

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

General and conversion

Timestream 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 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 value down to a multiple of the given bin interval.

SELECT bin(current_timestamp, 1day)

Example result: 2022-08-02 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 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 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