Date and time functions - Amazon Redshift

Date and time functions

In this section, you can find information about the date and time scalar functions that Amazon Redshift supports.

Summary of date and time functions

Function Syntax Returns
+ (Concatenation) operator

Concatenates a date to a time on either side of the + symbol and returns a TIMESTAMP or TIMESTAMPTZ.

date + time TIMESTAMP or TIMESTAMPZ
ADD_MONTHS

Adds the specified number of months to a date or timestamp.

ADD_MONTHS ({date|timestamp}, integer) TIMESTAMP
AT TIME ZONE

Specifies which time zone to use with a TIMESTAMP or TIMESTAMPTZ expression.

AT TIME ZONE 'timezone' TIMESTAMP or TIMESTAMPZ
CONVERT_TIMEZONE

Converts a timestamp from one time zone to another.

CONVERT_TIMEZONE (['timezone',] 'timezone', timestamp) TIMESTAMP
CURRENT_DATE

Returns a date in the current session time zone (UTC by default) for the start of the current transaction.

CURRENT_DATE DATE
DATE_CMP

Compares two dates and returns 0 if the dates are identical, 1 if date1 is greater, and -1 if date2 is greater.

DATE_CMP (date1, date2) INTEGER
DATE_CMP_TIMESTAMP

Compares a date to a time and returns 0 if the values are identical, 1 if date is greater and -1 if timestamp is greater.

DATE_CMP_TIMESTAMP (date, timestamp) INTEGER
DATE_CMP_TIMESTAMPTZ

Compares a date and a timestamp with time zone and returns 0 if the values are identical, 1 if date is greater and -1 if timestamptz is greater.

DATE_CMP_TIMESTAMPTZ (date, timestamptz) INTEGER
DATE_PART_YEAR

Extracts the year from a date.

DATE_PART_YEAR (date) INTEGER
DATEADD

Increments a date or time by a specified interval.

DATEADD (datepart, interval, {date|time|timetz|timestamp}) TIMESTAMP or TIME or TIMETZ
DATEDIFF

Returns the difference between two dates or times for a given date part, such as a day or month.

DATEDIFF (datepart, {date|time|timetz|timestamp}, {date|time|timetz|timestamp}) BIGINT
DATE_PART

Extracts a date part value from a date or time.

DATE_PART (datepart, {date|timestamp}) DOUBLE
DATE_TRUNC

Truncates a timestamp based on a date part.

DATE_TRUNC ('datepart', timestamp) TIMESTAMP
EXTRACT

Extracts a date part from a timestamp, time, timetz, or literal.

EXTRACT (datepart FROM {TIMESTAMP 'literal' | timestamp | time | timetz}) INTEGER or DOUBLE
GETDATE

Returns the current date and time in the current session time zone (UTC by default). The parentheses are required.

GETDATE() TIMESTAMP
INTERVAL_CMP

Compares two intervals and returns 0 if the intervals are equal, 1 if interval1 is greater, and -1 if interval2 is greater.

INTERVAL_CMP (interval1, interval2) INTEGER
LAST_DAY

Returns the date of the last day of the month that contains date.

LAST_DAY(date) DATE
MONTHS_BETWEEN

Returns the number of months between two dates.

MONTHS_BETWEEN (date, date) FLOAT8
NEXT_DAY

Returns the date of the first instance of day that is later than date.

NEXT_DAY (date, day) DATE
SYSDATE

Returns the date and time in UTC for the start of the current transaction.

SYSDATE TIMESTAMP
TIMEOFDAY

Returns the current weekday, date, and time in the current session time zone (UTC by default) as a string value.

TIMEOFDAY() VARCHAR
TIMESTAMP_CMP

Compares two timestamps and returns 0 if the timestamps are equal, 1 if interval1 is greater, and -1 if interval2 is greater.

TIMESTAMP_CMP (timestamp1, timestamp2) INTEGER
TIMESTAMP_CMP_DATE

Compares a timestamp to a date and returns 0 if the values are identical, 1 if timestamp is greater, and -1 if date is greater.

TIMESTAMP_CMP_DATE (timestamp, date) INTEGER
TIMESTAMP_CMP_TIMESTAMPTZ

Compares a timestamp with a timestamp with time zone and returns 0 if the values are equal, 1 if timestamp is greater, and -1 if timestamptz is greater.

TIMESTAMP_CMP_TIMESTAMPTZ (timestamp, timestamptz) INTEGER
TIMESTAMPTZ_CMP

Compares two timestamp with time zone values and returns 0 if the values are equal, 1 if timestamptz1 is greater, and -1 if timestamptz2 is greater.

TIMESTAMPTZ_CMP (timestamptz1, timestamptz2) INTEGER
TIMESTAMPTZ_CMP_DATE

Compares the value of a timestamp with time zone and a date and returns 0 if the values are equal, 1 if timestamptz is greater, and -1 if date is greater.

TIMESTAMPTZ_CMP_DATE (timestamptz, date) INTEGER
TIMESTAMPTZ_CMP_TIMESTAMP

Compares a timestamp with time zone with a timestamp and returns 0 if the values are equal, 1 if timestamptz is greater, and -1 if timestamp is greater.

TIMESTAMPTZ_CMP_TIMESTAMP (timestamptz, timestamp) INTEGER
TIMEZONE

Returns a timestamp for the specified time zone and timestamp value.

TIMEZONE ('timezone' { timestamp | timestamptz ) TIMESTAMP or TIMESTAMPTZ
TO_TIMESTAMP

Returns a timestamp with time zone for the specified timestamp and time zone format.

TO_TIMESTAMP ('timestamp', 'format') TIMESTAMPTZ
TRUNC

Truncates a timestamp and returns a date.

TRUNC(timestamp) DATE
Note

Leap seconds are not considered in elapsed-time calculations.

Date and time functions in transactions

When you run the following functions within a transaction block (BEGIN … END), the function returns the start date or time of the current transaction, not the start of the current statement.

  • SYSDATE

  • TIMESTAMP

  • CURRENT_DATE

The following functions always return the start date or time of the current statement, even when they are within a transaction block.

  • GETDATE

  • TIMEOFDAY

Deprecated leader node-only functions

The following date functions are deprecated because they run only on the leader node. For more information, see Leader node–only functions.