Date and time functions

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

Topics

• Summary of date and time functions
• Date and time functions in transactions
• Deprecated leader node-only functions
• + (Concatenation) operator
• ADD_MONTHS function
• AT TIME ZONE function
• CONVERT_TIMEZONE function
• CURRENT_DATE function
• DATE_CMP function
• DATE_CMP_TIMESTAMP function
• DATE_CMP_TIMESTAMPTZ function
• DATEADD function
• DATEDIFF function
• DATE_PART function
• DATE_PART_YEAR function
• DATE_TRUNC function
• EXTRACT function
• GETDATE function
• INTERVAL_CMP function
• LAST_DAY function
• MONTHS_BETWEEN function
• NEXT_DAY function
• SYSDATE function
• TIMEOFDAY function
• TIMESTAMP_CMP function
• TIMESTAMP_CMP_DATE function
• TIMESTAMP_CMP_TIMESTAMPTZ function
• TIMESTAMPTZ_CMP function
• TIMESTAMPTZ_CMP_DATE function
• TIMESTAMPTZ_CMP_TIMESTAMP function
• TIMEZONE function
• TO_TIMESTAMP function
• TRUNC function
• Date parts for date or timestamp functions

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 or time part from a timestamp, timestamptz, time, or timetz.	EXTRACT (datepart FROM source)	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 timestamp1 is greater, and -1 if timestamp2 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.

• AGE. Use DATEDIFF function instead.

• CURRENT_TIME. Use GETDATE function or SYSDATE instead.

• CURRENT_TIMESTAMP. Use GETDATE function or SYSDATE instead.

• LOCALTIME. Use GETDATE function or SYSDATE instead.

• LOCALTIMESTAMP. Use GETDATE function or SYSDATE instead.

• ISFINITE

• NOW. Use GETDATE function or SYSDATE instead.