Amazon Redshift will no longer support the creation of new Python UDFs starting November 1, 2025.
If you would like to use Python UDFs, create the UDFs prior to that date.
Existing Python UDFs will continue to function as normal. For more information, see the
blog post
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
| 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 |
DATE_CMP (date1, date2) | INTEGER |
| DATE_CMP_TIMESTAMP Compares a date to a time
and returns |
DATE_CMP_TIMESTAMP (date, timestamp) | INTEGER |
| DATE_CMP_TIMESTAMPTZ Compares a date and a
timestamp with time zone and returns |
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 |
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 |
TIMESTAMP_CMP (timestamp1, timestamp2) | INTEGER |
| TIMESTAMP_CMP_DATE Compares a timestamp to a
date and returns |
TIMESTAMP_CMP_DATE (timestamp, date) | INTEGER |
| TIMESTAMP_CMP_TIMESTAMPTZ Compares a
timestamp with a timestamp with time zone and returns |
TIMESTAMP_CMP_TIMESTAMPTZ (timestamp, timestamptz) | INTEGER |
| TIMESTAMPTZ_CMP Compares two timestamp with
time zone values and returns |
TIMESTAMPTZ_CMP (timestamptz1, timestamptz2) | INTEGER |
| TIMESTAMPTZ_CMP_DATE Compares the value of a
timestamp with time zone and a date and returns |
TIMESTAMPTZ_CMP_DATE (timestamptz, date) | INTEGER |
| TIMESTAMPTZ_CMP_TIMESTAMP Compares a
timestamp with time zone with a timestamp and returns |
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. If you use the NOW function within a materialized view, it sets to the timestamp of the creation of the materialized view, instead of the current timestamp.
