Menu
Amazon Redshift
Database Developer Guide (API Version 2012-12-01)

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 Description
ADD_MONTHS ADD_MONTHS ({date|timestamp}, integer) TIMESTAMP Adds the specified number of months to a date or time stamp.
AT TIME ZONE AT TIME ZONE 'timezone' TIMESTAMP Specifies which time zone to use with a TIMESTAMP or TIMESTAMPTZ expression.
CONVERT_TIMEZONE CONVERT_TIMEZONE (['timezone',] 'timezone', timestamp) TIMESTAMP Converts a time stamp from one time zone to another.
CURRENT_DATE CURRENT_DATE DATE Returns a date in the current session time zone (UTC by default) for the start of the current transaction.
DATE_CMP DATE_CMP (date1, date2) INTEGER Compares two dates and returns 0 if the dates are identical, 1 if date1 is greater, and -1 if date2 is greater.
DATE_CMP_TIMESTAMP DATE_CMP_TIMESTAMP (date, timestamp) INTEGER 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_TIMESTAMPTZ DATE_CMP_TIMESTAMPTZ (date, timestamptz) INTEGER Compares a date and a time stamp with time zone and returns 0 if the values are identical, 1 if date is greater and -1 if timestamptz is greater.
DATE_PART_YEAR DATE_PART_YEAR (date) INTEGER Extracts the year from a date.
DATEADD DATEADD (datepart, interval, {date|timestamp}) TIMESTAMP Increments a date or time by a specified interval.
DATEDIFF DATEDIFF (datepart, {date|time}, {date|timestamp}) BIGINT Returns the difference between two dates or times for a given date part, such as a day or month.
DATE_PART DATE_PART (datepart, {date|time}) DOUBLE Extracts a date part value from date or time.
DATE_TRUNC DATE_TRUNC ('datepart', timestamp) TIMESTAMP Truncates a time stamp based on a date part.
EXTRACT EXTRACT (datepart FROM {TIMESTAMP 'literal' | timestamp}) DOUBLE Extracts a date part from a timestamp or literal.
GETDATE GETDATE() TIMESTAMP Returns the current date and time in the current session time zone (UTC by default). The parentheses are required.
INTERVAL_CMP INTERVAL_CMP (interval1, interval2) INTEGER Compares two intervals and returns 0 if the intervals are equal, 1 if interval1 is greater, and -1 if interval2 is greater.
LAST_DAY LAST_DAY(date) DATE Returns the date of the last day of the month that contains date.
MONTHS_BETWEEN MONTHS_BETWEEN (date, date) FLOAT8 Returns the number of months between two dates.
NEXT_DAY NEXT_DAY (date, day) DATE Returns the date of the first instance of day that is later than date.
SYSDATE SYSDATE TIMESTAMP Returns the date and time in the current session time zone (UTC by default) for the start of the current transaction.
TIMEOFDAY TIMEOFDAY() VARCHAR Returns the current weekday, date, and time in the current session time zone (UTC by default) as a string value.
TIMESTAMP_CMP TIMESTAMP_CMP (timestamp1, timestamp2) INTEGER Compares two timestamps and returns 0 if the timestamps are equal, 1 if timestamp1 is greater, and -1 if timestamp2 is greater.
TIMESTAMP_CMP_DATE TIMESTAMP_CMP_DATE (timestamp, date) INTEGER Compares a timestamp to a date and returns 0 if the values are equal, 1 if timestamp is greater, and -1 if date is greater.
TIMESTAMP_CMP_TIMESTAMPTZ TIMESTAMP_CMP_TIMESTAMPTZ (timestamp, timestamptz) INTEGER Compares a timestamp with a time stamp with time zone and returns 0 if the values are equal, 1 if timestamp is greater, and -1 if timestamptz is greater.
TIMESTAMPTZ_CMP TIMESTAMPTZ_CMP (timestamptz1, timestamptz2) INTEGER 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_DATE TIMESTAMPTZ_CMP_DATE (timestamptz, date) INTEGER Compares the value of a time stamp 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_TIMESTAMP TIMESTAMPTZ_CMP_TIMESTAMP (timestamptz, timestamp) INTEGER Compares a timestamp with time zone with a time stamp and returns 0 if the values are equal, 1 if timestamptz is greater, and -1 if timestamp is greater.
TIMEZONE TIMEZONE ('timezone' { timestamp | timestamptz ) INTEGER Returns a time stamp for the specified time zone and time stamp value.
TO_TIMESTAMP TO_TIMESTAMP ('timestamp', 'format') INTEGER Returns a time stamp with time zone for the specified time stamp and time zone format.
TRUNC TRUNC(timestamp) DATE Truncates a time stamp and returns a date.

Note

Leap seconds are not considered in elapsed-time calculations.

Date and Time Functions in Transactions

When you execute 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 execute only on the leader node. For more information, see Leader Node–Only Functions.