Amazon Redshift will no longer support the creation of new Python UDFs starting Patch 198. Existing Python UDFs will continue to function until June 30, 2026. For more information, see the [ blog post ](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/).
# 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-functions-summary)
+ [Date and time functions in transactions](#date-functions-transactions)
+ [Deprecated leader node-only functions](#date-functions-deprecated)
+ [\$1 (Concatenation) operator](r_DATE-CONCATENATE_function.md)
+ [ADD\$1MONTHS function](r_ADD_MONTHS.md)
+ [AT TIME ZONE function](r_AT_TIME_ZONE.md)
+ [CONVERT\$1TIMEZONE function](CONVERT_TIMEZONE.md)
+ [CURRENT\$1DATE function](r_CURRENT_DATE_function.md)
+ [DATE\$1CMP function](r_DATE_CMP.md)
+ [DATE\$1CMP\$1TIMESTAMP function](r_DATE_CMP_TIMESTAMP.md)
+ [DATE\$1CMP\$1TIMESTAMPTZ function](r_DATE_CMP_TIMESTAMPTZ.md)
+ [DATEADD function](r_DATEADD_function.md)
+ [DATEDIFF function](r_DATEDIFF_function.md)
+ [DATE\$1PART function](r_DATE_PART_function.md)
+ [DATE\$1PART\$1YEAR function](r_DATE_PART_YEAR.md)
+ [DATE\$1TRUNC function](r_DATE_TRUNC.md)
+ [EXTRACT function](r_EXTRACT_function.md)
+ [GETDATE function](r_GETDATE.md)
+ [INTERVAL\$1CMP function](r_INTERVAL_CMP.md)
+ [LAST\$1DAY function](r_LAST_DAY.md)
+ [MONTHS\$1BETWEEN function](r_MONTHS_BETWEEN_function.md)
+ [NEXT\$1DAY function](r_NEXT_DAY.md)
+ [SYSDATE function](r_SYSDATE.md)
+ [TIMEOFDAY function](r_TIMEOFDAY_function.md)
+ [TIMESTAMP\$1CMP function](r_TIMESTAMP_CMP.md)
+ [TIMESTAMP\$1CMP\$1DATE function](r_TIMESTAMP_CMP_DATE.md)
+ [TIMESTAMP\$1CMP\$1TIMESTAMPTZ function](r_TIMESTAMP_CMP_TIMESTAMPTZ.md)
+ [TIMESTAMPTZ\$1CMP function](r_TIMESTAMPTZ_CMP.md)
+ [TIMESTAMPTZ\$1CMP\$1DATE function](r_TIMESTAMPTZ_CMP_DATE.md)
+ [TIMESTAMPTZ\$1CMP\$1TIMESTAMP function](r_TIMESTAMPTZ_CMP_TIMESTAMP.md)
+ [TIMEZONE function](r_TIMEZONE.md)
+ [TO\$1TIMESTAMP function](r_TO_TIMESTAMP.md)
+ [TRUNC function](r_TRUNC_date.md)
+ [Date parts for date or timestamp functions](r_Dateparts_for_datetime_functions.md)
## Summary of date and time functions
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/Date_functions_header.html)
**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\$1DATE
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](c_SQL_functions_leader_node_only.md).
+ AGE. Use [DATEDIFF function](r_DATEDIFF_function.md) instead.
+ CURRENT\$1TIME. Use [GETDATE function](r_GETDATE.md) or [SYSDATE](r_SYSDATE.md) instead.
+ CURRENT\$1TIMESTAMP. Use [GETDATE function](r_GETDATE.md) or [SYSDATE](r_SYSDATE.md) instead.
+ LOCALTIME. Use [GETDATE function](r_GETDATE.md) or [SYSDATE](r_SYSDATE.md) instead.
+ LOCALTIMESTAMP. Use [GETDATE function](r_GETDATE.md) or [SYSDATE](r_SYSDATE.md) instead.
+ ISFINITE
+ NOW. Use [GETDATE function](r_GETDATE.md) or [SYSDATE](r_SYSDATE.md) 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.
# \$1 (Concatenation) operator
Concatenates a DATE to a TIME or TIMETZ on either side of the \$1 symbol and returns a TIMESTAMP or TIMESTAMPTZ.
## Syntax
```
date + {time | timetz}
```
The order of the arguments can be reversed. For example, *time* \$1 *date*.
## Arguments
*date*
A column of data type `DATE` or an expression that implicitly evaluates to a `DATE` type.
*time*
A column of data type `TIME` or an expression that implicitly evaluates to a `TIME` type.
*timetz*
A column of data type `TIMETZ` or an expression that implicitly evaluates to a `TIMETZ` type.
## Return type
TIMESTAMP if input is *date* \$1 *time*.
TIMESTAMPTZ if input is *date* \$1 *timetz*.
## Examples
### Example setup
To set up the TIME\$1TEST and TIMETZ\$1TEST tables used in the examples, use the following command.
```
create table time_test(time_val time);
insert into time_test values
('20:00:00'),
('00:00:00.5550'),
('00:58:00');
create table timetz_test(timetz_val timetz);
insert into timetz_test values
('04:00:00+00'),
('00:00:00.5550+00'),
('05:58:00+00');
```
### Examples with a time column
The following example table TIME\$1TEST has a column TIME\$1VAL (type TIME) with three values inserted.
```
select time_val from time_test;
time_val
---------------------
20:00:00
00:00:00.5550
00:58:00
```
The following example concatenates a date literal and a TIME\$1VAL column.
```
select date '2000-01-02' + time_val as ts from time_test;
ts
---------------------
2000-01-02 20:00:00
2000-01-02 00:00:00.5550
2000-01-02 00:58:00
```
The following example concatenates a date literal and a time literal.
```
select date '2000-01-01' + time '20:00:00' as ts;
ts
---------------------
2000-01-01 20:00:00
```
The following example concatenates a time literal and a date literal.
```
select time '20:00:00' + date '2000-01-01' as ts;
ts
---------------------
2000-01-01 20:00:00
```
### Examples with a TIMETZ column
The following example table TIMETZ\$1TEST has a column TIMETZ\$1VAL (type TIMETZ) with three values inserted.
```
select timetz_val from timetz_test;
timetz_val
------------------
04:00:00+00
00:00:00.5550+00
05:58:00+00
```
The following example concatenates a date literal and a TIMETZ\$1VAL column.
```
select date '2000-01-01' + timetz_val as ts from timetz_test;
ts
---------------------
2000-01-01 04:00:00+00
2000-01-01 00:00:00.5550+00
2000-01-01 05:58:00+00
```
The following example concatenates a TIMETZ\$1VAL column and a date literal.
```
select timetz_val + date '2000-01-01' as ts from timetz_test;
ts
---------------------
2000-01-01 04:00:00+00
2000-01-01 00:00:00.5550+00
2000-01-01 05:58:00+00
```
The following example concatenates a DATE literal and a TIMETZ literal. The example returns a TIMESTAMPTZ which is in the time zone UTC by default. UTC is 8 hours ahead of PST, so the result is 8 hours ahead of the input time.
```
select date '2000-01-01' + timetz '20:00:00 PST' as ts;
ts
------------------------
2000-01-02 04:00:00+00
```
# ADD\$1MONTHS function
ADD\$1MONTHS adds the specified number of months to a date or timestamp value or expression. The [DATEADD](r_DATEADD_function.md) function provides similar functionality.
## Syntax
```
ADD_MONTHS( {date | timestamp}, integer)
```
## Arguments
*date* \$1 *timestamp*
A column of data type `DATE` or `TIMESTAMP` or an expression that implicitly evaluates to a `DATE` or `TIMESTAMP` type. If the date is the last day of the month, or if the resulting month is shorter, the function returns the last day of the month in the result. For other dates, the result contains the same day number as the date expression.
*integer*
A value of data type `INTEGER`. Use a negative number to subtract months from dates.
## Return type
TIMESTAMP
## Examples
The following query uses the ADD\$1MONTHS function inside a TRUNC function. The TRUNC function removes the time of day from the result of ADD\$1MONTHS. The ADD\$1MONTHS function adds 12 months to each value from the CALDATE column. The values in the CALDATE column are dates.
```
select distinct trunc(add_months(caldate, 12)) as calplus12,
trunc(caldate) as cal
from date
order by 1 asc;
calplus12 | cal
------------+------------
2009-01-01 | 2008-01-01
2009-01-02 | 2008-01-02
2009-01-03 | 2008-01-03
...
(365 rows)
```
The following example uses the ADD\$1MONTHS function to add 1 month to a *timestamp*.
```
select add_months('2008-01-01 05:07:30', 1);
add_months
---------------------
2008-02-01 05:07:30
```
The following examples demonstrate the behavior when the ADD\$1MONTHS function operates on dates with months that have different numbers of days. This example shows how the function handles adding 1 month to March 31 and adding 1 month to April 30. April has 30 days, so adding 1 month to March 31 results in April 30. May has 31 days, so adding 1 month to April 30 results in May 31.
```
select add_months('2008-03-31',1);
add_months
---------------------
2008-04-30 00:00:00
select add_months('2008-04-30',1);
add_months
---------------------
2008-05-31 00:00:00
```
# AT TIME ZONE function
AT TIME ZONE specifies which time zone to use with a TIMESTAMP or TIMESTAMPTZ expression.
## Syntax
```
AT TIME ZONE 'timezone'
```
## Arguments
*timezone*
The `TIMEZONE` for the return value. The time zone can be specified as a time zone name (such as **'Africa/Kampala'** or **'Singapore'**) or as a time zone abbreviation (such as **'UTC'** or **'PDT'**).
To view a list of supported time zone names, run the following command.
```
select pg_timezone_names();
```
To view a list of supported time zone abbreviations, run the following command.
```
select pg_timezone_abbrevs();
```
For more information and examples, see [Time zone usage notes](CONVERT_TIMEZONE.md#CONVERT_TIMEZONE-usage-notes).
## Return type
TIMESTAMPTZ when used with a TIMESTAMP expression. TIMESTAMP when used with a TIMESTAMPTZ expression.
## Examples
The following example converts a timestamp value without time zone and interprets it as MST time (UTC\$17 in POSIX). The example returns a value of data type TIMESTAMPTZ for the UTC timezone. If you configure your default timezone to a timezone other than UTC, you might see a different result.
```
SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'MST';
timezone
------------------------
2001-02-17 03:38:40+00
```
The following example takes an input timestamp with a time zone value where the specified time zone is EST (UTC\$15 in POSIX) and converts it to MST (UTC\$17 in POSIX). The example returns a value of data type TIMESTAMP.
```
SELECT TIMESTAMPTZ '2001-02-16 20:38:40-05' AT TIME ZONE 'MST';
timezone
------------------------
2001-02-16 18:38:40
```
# CONVERT\$1TIMEZONE function
CONVERT\$1TIMEZONE converts a timestamp from one time zone to another. The function automatically adjusts for daylight saving time.
## Syntax
```
CONVERT_TIMEZONE( ['source_timezone',] 'target_timezone', 'timestamp')
```
## Arguments
*source\$1timezone*
(Optional) The time zone of the current timestamp. The default is UTC. For more information, see [Time zone usage notes](#CONVERT_TIMEZONE-usage-notes).
*target\$1timezone*
The time zone for the new timestamp. For more information, see [Time zone usage notes](#CONVERT_TIMEZONE-usage-notes).
*timestamp*
A timestamp column or an expression that implicitly converts to a timestamp.
## Return type
TIMESTAMP
## Time zone usage notes
*source\$1timezone* or *target\$1timezone* can be specified as a time zone name (such as 'Africa/Kampala' or 'Singapore') or as a time zone abbreviation (such as 'UTC' or 'PDT'). You don't have to convert time zone names to names or abbreviations to abbreviations. For example, you can choose a timestamp from the source time zone name 'Singapore' and convert it to a timestamp in the time zone abbreviation 'PDT'.
**Note**
The results of using a time zone name or a time zone abbreviation can be different due to local seasonal time, such as daylight saving time.
### Using a time zone name
To view a current and complete list of time zone names, run the following command.
```
select pg_timezone_names();
```
Each row contains a comma-separated string with the time zone name, abbreviation, UTC offset, and indicator if the time zone observes daylight saving time (`t` or `f`). For example, the following snippet shows two resulting rows. The first row is the time zone `Antarctica/South Pole`, abbreviation `NZDT`, with `13:00:00` offset from UTC, and `f` to indicate it doesn't observe daylight saving time. The second row is the time zone `Europe/Paris`, abbreviation `CET`, with `01:00:00` offset from UTC, and `f` to indicate it observes daylight saving time.
```
pg_timezone_names
------------------
(Antarctica/South_Pole,NZDT,13:00:00,t)
(Europe/Paris,CET,01:00:00,f)
```
Run the SQL statement to obtain the entire list and find a time zone name. Approximately 600 rows are returned. Even though some of the returned time zone names are capitalized initialisms or acronyms (for example; GB, PRC, ROK), the CONVERT\$1TIMEZONE function treats them as time zone names, not time zone abbreviations.
If you specify a time zone using a time zone name, CONVERT\$1TIMEZONE automatically adjusts for daylight saving time (DST), or any other local seasonal protocol, such as Summer Time, Standard Time, or Winter Time, that is in force for that time zone during the date and time specified by '*timestamp*'. For example, 'Europe/London' represents UTC in the winter and adds one hour in the summer. Note that Amazon Redshift uses the [IANA Time Zone Database](https://www.iana.org/time-zones) as the authoritative source of time zone specification.
### Using a time zone abbreviation
To view a current and complete list of time zone abbreviations, run the following command.
```
select pg_timezone_abbrevs();
```
The results contain a comma-separated string with the time zone abbreviation, UTC offset, and indicator if the time zone observes daylight saving time (`t` or `f`). For example, the following snippet shows two resulting rows. The first row contains the abbreviation for Pacific Daylight Time `PDT`, with a `-07:00:00` offset from UTC, and `t` to indicate it observes daylight saving time. The second row contains the abbreviation for Pacific Standard Time `PST`, with a `-08:00:00` offset from UTC, and `f` to indicate it doesn't observe daylight saving time.
```
pg_timezone_abbrevs
--------------------
(PDT,-07:00:00,t)
(PST,-08:00:00,f)
```
Run the SQL statement to obtain the entire list and find an abbreviation based on its offset and daylight saving time indicator. Approximately 200 rows are returned.
Time zone abbreviations represent a fixed offset from UTC. If you specify a time zone using a time zone abbreviation, CONVERT\$1TIMEZONE uses the fixed offset from UTC and doesn't adjust for any local seasonal protocol.
### Using POSIX-style format
A POSIX-style time zone specification is in the form *STDoffset* or *STDoffsetDST*, where *STD* is a time zone abbreviation, *offset* is the numeric offset in hours west from UTC, and *DST* is an optional daylight saving zone abbreviation. Daylight saving time is assumed to be one hour ahead of the given offset.
POSIX-style time zone formats use positive offsets west of Greenwich, in contrast to the ISO-8601 convention, which uses positive offsets east of Greenwich.
The following are examples of POSIX-style time zones:
+ PST8
+ PST8PDT
+ EST5
+ EST5EDT
**Note**
Amazon Redshift doesn't validate POSIX-style time zone specifications, so it is possible to set the time zone to an invalid value. For example, the following command doesn't return an error, even though it sets the time zone to an invalid value.
```
set timezone to ‘xxx36’;
```
## Examples
Many of the examples use the TICKIT sample data set. For more information, see [Sample database](https://docs.aws.amazon.com/redshift/latest/dg/c_sampledb.html).
The following example converts the timestamp value from the default UTC time zone to PST.
```
select convert_timezone('PST', '2008-08-21 07:23:54');
convert_timezone
------------------------
2008-08-20 23:23:54
```
The following example converts the timestamp value in the LISTTIME column from the default UTC time zone to PST. Though the timestamp is within the daylight time period, it's converted to standard time because the target time zone is specified as an abbreviation (PST).
```
select listtime, convert_timezone('PST', listtime) from listing
where listid = 16;
listtime | convert_timezone
--------------------+-------------------
2008-08-24 09:36:12 2008-08-24 01:36:12
```
The following example converts a timestamp LISTTIME column from the default UTC time zone to US/Pacific time zone. The target time zone uses a time zone name, and the timestamp is within the daylight time period, so the function returns the daylight time.
```
select listtime, convert_timezone('US/Pacific', listtime) from listing
where listid = 16;
listtime | convert_timezone
--------------------+---------------------
2008-08-24 09:36:12 | 2008-08-24 02:36:12
```
The following example converts a timestamp string from EST to PST:
```
select convert_timezone('EST', 'PST', '20080305 12:25:29');
convert_timezone
-------------------
2008-03-05 09:25:29
```
The following example converts a timestamp to US Eastern Standard Time because the target time zone uses a time zone name (America/New\$1York) and the timestamp is within the standard time period.
```
select convert_timezone('America/New_York', '2013-02-01 08:00:00');
convert_timezone
---------------------
2013-02-01 03:00:00
(1 row)
```
The following example converts the timestamp to US Eastern Daylight Time because the target time zone uses a time zone name (America/New\$1York) and the timestamp is within the daylight time period.
```
select convert_timezone('America/New_York', '2013-06-01 08:00:00');
convert_timezone
---------------------
2013-06-01 04:00:00
(1 row)
```
The following example demonstrates the use of offsets.
```
SELECT CONVERT_TIMEZONE('GMT','NEWZONE +2','2014-05-17 12:00:00') as newzone_plus_2,
CONVERT_TIMEZONE('GMT','NEWZONE-2:15','2014-05-17 12:00:00') as newzone_minus_2_15,
CONVERT_TIMEZONE('GMT','America/Los_Angeles+2','2014-05-17 12:00:00') as la_plus_2,
CONVERT_TIMEZONE('GMT','GMT+2','2014-05-17 12:00:00') as gmt_plus_2;
newzone_plus_2 | newzone_minus_2_15 | la_plus_2 | gmt_plus_2
---------------------+---------------------+---------------------+---------------------
2014-05-17 10:00:00 | 2014-05-17 14:15:00 | 2014-05-17 10:00:00 | 2014-05-17 10:00:00
(1 row)
```
# CURRENT\$1DATE function
CURRENT\$1DATE returns a date in the current session time zone (UTC by default) in the default format: YYYY-MM-DD.
**Note**
CURRENT\$1DATE returns the start date for the current transaction, not for the start of the current statement. Consider the scenario where you start a transaction containing multiple statements on 10/01/08 23:59, and the statement containing CURRENT\$1DATE runs at 10/02/08 00:00. CURRENT\$1DATE returns `10/01/08`, not `10/02/08`.
## Syntax
```
CURRENT_DATE
```
## Return type
DATE
## Examples
The following example returns the current date (in the AWS Region where the function runs).
```
select current_date;
date
------------
2008-10-01
```
The following example creates a table, inserts a row where the default of column `todays_date` is CURRENT\$1DATE, and then selects all the rows in the table.
```
CREATE TABLE insert_dates(
label varchar(128) NOT NULL,
todays_date DATE DEFAULT CURRENT_DATE);
INSERT INTO insert_dates(label)
VALUES('Date row inserted');
SELECT * FROM insert_dates;
label | todays_date
------------------+-------------
Date row inserted | 2023-05-10
```
# DATE\$1CMP function
DATE\$1CMP compares two dates. The function returns `0` if the dates are identical, `1` if *date1* is greater, and `-1` if *date2* is greater.
## Syntax
```
DATE_CMP(date1, date2)
```
## Arguments
*date1*
A column of data type `DATE` or an expression that evaluates to a `DATE` type.
*date2*
A column of data type `DATE` or an expression that evaluates to a `DATE` type.
## Return type
INTEGER
## Examples
The following query compares the DATE values in the CALDATE column to the date January 4, 2008 and returns whether the value in CALDATE is before (`-1`), equal to (`0`), or after (`1`) January 4, 2008:
```
select caldate, '2008-01-04',
date_cmp(caldate,'2008-01-04')
from date
order by dateid
limit 10;
caldate | ?column? | date_cmp
-----------+------------+----------
2008-01-01 | 2008-01-04 | -1
2008-01-02 | 2008-01-04 | -1
2008-01-03 | 2008-01-04 | -1
2008-01-04 | 2008-01-04 | 0
2008-01-05 | 2008-01-04 | 1
2008-01-06 | 2008-01-04 | 1
2008-01-07 | 2008-01-04 | 1
2008-01-08 | 2008-01-04 | 1
2008-01-09 | 2008-01-04 | 1
2008-01-10 | 2008-01-04 | 1
(10 rows)
```
# DATE\$1CMP\$1TIMESTAMP function
DATE\$1CMP\$1TIMESTAMP compares a date to a timestamp and returns `0` if the values are identical, `1` if *date* is greater chronologically and `-1` if *timestamp* is greater.
## Syntax
```
DATE_CMP_TIMESTAMP(date, timestamp)
```
## Arguments
*date*
A column of data type `DATE` or an expression that evaluates to a `DATE` type.
*timestamp*
A column of data type `TIMESTAMP` or an expression that evaluates to a `TIMESTAMP` type.
## Return type
INTEGER
## Examples
The following example compares the date `2008-06-18` to LISTTIME. The values of the column LISTTIME are timestamps. Listings made before this date return `1`; listings made after this date return `-1`.
```
select listid, '2008-06-18', listtime,
date_cmp_timestamp('2008-06-18', listtime)
from listing
order by 1, 2, 3, 4
limit 10;
listid | ?column? | listtime | date_cmp_timestamp
--------+------------+---------------------+--------------------
1 | 2008-06-18 | 2008-01-24 06:43:29 | 1
2 | 2008-06-18 | 2008-03-05 12:25:29 | 1
3 | 2008-06-18 | 2008-11-01 07:35:33 | -1
4 | 2008-06-18 | 2008-05-24 01:18:37 | 1
5 | 2008-06-18 | 2008-05-17 02:29:11 | 1
6 | 2008-06-18 | 2008-08-15 02:08:13 | -1
7 | 2008-06-18 | 2008-11-15 09:38:15 | -1
8 | 2008-06-18 | 2008-11-09 05:07:30 | -1
9 | 2008-06-18 | 2008-09-09 08:03:36 | -1
10 | 2008-06-18 | 2008-06-17 09:44:54 | 1
(10 rows)
```
# DATE\$1CMP\$1TIMESTAMPTZ function
DATE\$1CMP\$1TIMESTAMPTZ compares a date to a timestamp with time zone and returns `0` if the values are identical, `1` if *date* is greater chronologically and `-1` if *timestamptz* is greater.
## Syntax
```
DATE_CMP_TIMESTAMPTZ(date, timestamptz)
```
## Arguments
*date*
A column of data type `DATE` or an expression that implicitly evaluates to a `DATE` type.
*timestamptz*
A column of data type `TIMESTAMPTZ` or an expression that implicitly evaluates to a `TIMESTAMPTZ` type.
## Return type
INTEGER
## Examples
The following example compares the date `2008-06-18` to LISTTIME. Listings made before this date return `1`; listings made after this date return `-1`.
```
select listid, '2008-06-18', CAST(listtime AS timestamptz),
date_cmp_timestamptz('2008-06-18', CAST(listtime AS timestamptz))
from listing
order by 1, 2, 3, 4
limit 10;
listid | ?column? | timestamptz | date_cmp_timestamptz
--------+------------+------------------------+----------------------
1 | 2008-06-18 | 2008-01-24 06:43:29+00 | 1
2 | 2008-06-18 | 2008-03-05 12:25:29+00 | 1
3 | 2008-06-18 | 2008-11-01 07:35:33+00 | -1
4 | 2008-06-18 | 2008-05-24 01:18:37+00 | 1
5 | 2008-06-18 | 2008-05-17 02:29:11+00 | 1
6 | 2008-06-18 | 2008-08-15 02:08:13+00 | -1
7 | 2008-06-18 | 2008-11-15 09:38:15+00 | -1
8 | 2008-06-18 | 2008-11-09 05:07:30+00 | -1
9 | 2008-06-18 | 2008-09-09 08:03:36+00 | -1
10 | 2008-06-18 | 2008-06-17 09:44:54+00 | 1
(10 rows)
```
# DATEADD function
Increments a DATE, TIME, TIMETZ, or TIMESTAMP value by a specified interval.
## Syntax
```
DATEADD( datepart, interval, {date|time|timetz|timestamp} )
```
## Arguments
*datepart*
The date part (year, month, day, or hour, for example) that the function operates on. For more information, see [Date parts for date or timestamp functions](r_Dateparts_for_datetime_functions.md).
*interval*
An integer that specified the interval (number of days, for example) to add to the target expression. A negative integer subtracts the interval.
*date*\$1*time*\$1*timetz*\$1*timestamp*
A DATE, TIME, TIMETZ, or TIMESTAMP column or an expression that implicitly converts to a DATE, TIME, TIMETZ, or TIMESTAMP. The DATE, TIME, TIMETZ, or TIMESTAMP expression must contain the specified date part.
## Return type
TIMESTAMP or TIME or TIMETZ depending on the input data type.
## Examples with a DATE column
The following example adds 30 days to each date in November that exists in the DATE table.
```
select dateadd(day,30,caldate) as novplus30
from date
where month='NOV'
order by dateid;
novplus30
---------------------
2008-12-01 00:00:00
2008-12-02 00:00:00
2008-12-03 00:00:00
...
(30 rows)
```
The following example adds 18 months to a literal date value.
```
select dateadd(month,18,'2008-02-28');
date_add
---------------------
2009-08-28 00:00:00
(1 row)
```
The default column name for a DATEADD function is DATE\$1ADD. The default timestamp for a date value is `00:00:00`.
The following example adds 30 minutes to a date value that doesn't specify a timestamp.
```
select dateadd(m,30,'2008-02-28');
date_add
---------------------
2008-02-28 00:30:00
(1 row)
```
You can name date parts in full or abbreviate them. In this case, *m* stands for minutes, not months.
## Examples with a TIME column
The following example table TIME\$1TEST has a column TIME\$1VAL (type TIME) with three values inserted.
```
select time_val from time_test;
time_val
---------------------
20:00:00
00:00:00.5550
00:58:00
```
The following example adds 5 minutes to each TIME\$1VAL in the TIME\$1TEST table.
```
select dateadd(minute,5,time_val) as minplus5 from time_test;
minplus5
---------------
20:05:00
00:05:00.5550
01:03:00
```
The following example adds 8 hours to a literal time value.
```
select dateadd(hour, 8, time '13:24:55');
date_add
---------------
21:24:55
```
The following example shows when a time goes over 24:00:00 or under 00:00:00.
```
select dateadd(hour, 12, time '13:24:55');
date_add
---------------
01:24:55
```
## Examples with a TIMETZ column
The output values in these examples are in UTC which is the default timezone.
The following example table TIMETZ\$1TEST has a column TIMETZ\$1VAL (type TIMETZ) with three values inserted.
```
select timetz_val from timetz_test;
timetz_val
------------------
04:00:00+00
00:00:00.5550+00
05:58:00+00
```
The following example adds 5 minutes to each TIMETZ\$1VAL in TIMETZ\$1TEST table.
```
select dateadd(minute,5,timetz_val) as minplus5_tz from timetz_test;
minplus5_tz
---------------
04:05:00+00
00:05:00.5550+00
06:03:00+00
```
The following example adds 2 hours to a literal timetz value.
```
select dateadd(hour, 2, timetz '13:24:55 PST');
date_add
---------------
23:24:55+00
```
## Examples with a TIMESTAMP column
The output values in these examples are in UTC which is the default timezone.
The following example table TIMESTAMP\$1TEST has a column TIMESTAMP\$1VAL (type TIMESTAMP) with three values inserted.
```
SELECT timestamp_val FROM timestamp_test;
timestamp_val
------------------
1988-05-15 10:23:31
2021-03-18 17:20:41
2023-06-02 18:11:12
```
The following example adds 20 years only to the TIMESTAMP\$1VAL values in TIMESTAMP\$1TEST from before the year 2000.
```
SELECT dateadd(year,20,timestamp_val)
FROM timestamp_test
WHERE timestamp_val < to_timestamp('2000-01-01 00:00:00', 'YYYY-MM-DD HH:MI:SS');
date_add
---------------
2008-05-15 10:23:31
```
The following example adds 5 seconds to a literal timestamp value written without a seconds indicator.
```
SELECT dateadd(second, 5, timestamp '2001-06-06');
date_add
---------------
2001-06-06 00:00:05
```
## Usage notes
The DATEADD(month, ...) and ADD\$1MONTHS functions handle dates that fall at the ends of months differently:
+ ADD\$1MONTHS: If the date you are adding to is the last day of the month, the result is always the last day of the result month, regardless of the length of the month. For example, April 30 \$1 1 month is May 31.
```
select add_months('2008-04-30',1);
add_months
---------------------
2008-05-31 00:00:00
(1 row)
```
+ DATEADD: If there are fewer days in the date you are adding to than in the result month, the result is the corresponding day of the result month, not the last day of that month. For example, April 30 \$1 1 month is May 30.
```
select dateadd(month,1,'2008-04-30');
date_add
---------------------
2008-05-30 00:00:00
(1 row)
```
The DATEADD function handles the leap year date 02-29 differently when using dateadd(month, 12,…) or dateadd(year, 1, …).
```
select dateadd(month,12,'2016-02-29');
date_add
---------------------
2017-02-28 00:00:00
select dateadd(year, 1, '2016-02-29');
date_add
---------------------
2017-03-01 00:00:00
```
# DATEDIFF function
DATEDIFF returns the difference between the date parts of two date or time expressions.
## Syntax
```
DATEDIFF( datepart, {date|time|timetz|timestamp}, {date|time|timetz|timestamp} )
```
## Arguments
*datepart*
The specific part of the date or time value (year, month, or day, hour, minute, second, millisecond, or microsecond) that the function operates on. For more information, see [Date parts for date or timestamp functions](r_Dateparts_for_datetime_functions.md).
Specifically, DATEDIFF determines the number of date part boundaries that are crossed between two expressions. For example, suppose that you're calculating the difference in years between two dates, `12-31-2008` and `01-01-2009`. In this case, the function returns 1 year despite the fact that these dates are only one day apart. If you are finding the difference in hours between two timestamps, `01-01-2009 8:30:00` and `01-01-2009 10:00:00`, the result is 2 hours. If you are finding the difference in hours between two timestamps, `8:30:00` and `10:00:00`, the result is 2 hours.
*date*\$1*time*\$1*timetz*\$1*timestamp*
A DATE, TIME, TIMETZ, or TIMESTAMP column or expressions that implicitly convert to a DATE, TIME, TIMETZ, or TIMESTAMP. The expressions must both contain the specified date or time part. If the second date or time is later than the first date or time, the result is positive. If the second date or time is earlier than the first date or time, the result is negative.
## Return type
BIGINT
## Examples with a DATE column
The following example finds the difference, in number of weeks, between two literal date values.
```
select datediff(week,'2009-01-01','2009-12-31') as numweeks;
numweeks
----------
52
(1 row)
```
The following example finds the difference, in hours, between two literal date values. When you don't provide the time value for a date, it defaults to 00:00:00.
```
select datediff(hour, '2023-01-01', '2023-01-03 05:04:03');
date_diff
----------
53
(1 row)
```
The following example finds the difference, in days, between two literal TIMESTAMETZ values.
```
Select datediff(days, 'Jun 1,2008 09:59:59 EST', 'Jul 4,2008 09:59:59 EST')
date_diff
----------
33
```
The following example finds the difference, in days, between two dates in the same row of a table.
```
select * from date_table;
start_date | end_date
-----------+-----------
2009-01-01 | 2009-03-23
2023-01-04 | 2024-05-04
(2 rows)
select datediff(day, start_date, end_date) as duration from date_table;
duration
---------
81
486
(2 rows)
```
The following example finds the difference, in number of quarters, between a literal value in the past and today's date. This example assumes that the current date is June 5, 2008. You can name date parts in full or abbreviate them. The default column name for the DATEDIFF function is DATE\$1DIFF.
```
select datediff(qtr, '1998-07-01', current_date);
date_diff
-----------
40
(1 row)
```
The following example joins the SALES and LISTING tables to calculate how many days after they were listed any tickets were sold for listings 1000 through 1005. The longest wait for sales of these listings was 15 days, and the shortest was less than one day (0 days).
```
select priceperticket,
datediff(day, listtime, saletime) as wait
from sales, listing where sales.listid = listing.listid
and sales.listid between 1000 and 1005
order by wait desc, priceperticket desc;
priceperticket | wait
---------------+------
96.00 | 15
123.00 | 11
131.00 | 9
123.00 | 6
129.00 | 4
96.00 | 4
96.00 | 0
(7 rows)
```
This example calculates the average number of hours sellers waited for all ticket sales.
```
select avg(datediff(hours, listtime, saletime)) as avgwait
from sales, listing
where sales.listid = listing.listid;
avgwait
---------
465
(1 row)
```
## Examples with a TIME column
The following example table TIME\$1TEST has a column TIME\$1VAL (type TIME) with three values inserted.
```
select time_val from time_test;
time_val
---------------------
20:00:00
00:00:00.5550
00:58:00
```
The following example finds the difference in number of hours between the TIME\$1VAL column and a time literal.
```
select datediff(hour, time_val, time '15:24:45') from time_test;
date_diff
-----------
-5
15
15
```
The following example finds the difference in number of minutes between two literal time values.
```
select datediff(minute, time '20:00:00', time '21:00:00') as nummins;
nummins
----------
60
```
## Examples with a TIMETZ column
The following example table TIMETZ\$1TEST has a column TIMETZ\$1VAL (type TIMETZ) with three values inserted.
```
select timetz_val from timetz_test;
timetz_val
------------------
04:00:00+00
00:00:00.5550+00
05:58:00+00
```
The following example finds the differences in number of hours, between a TIMETZ literal and timetz\$1val.
```
select datediff(hours, timetz '20:00:00 PST', timetz_val) as numhours from timetz_test;
numhours
----------
0
-4
1
```
The following example finds the difference in number of hours, between two literal TIMETZ values.
```
select datediff(hours, timetz '20:00:00 PST', timetz '00:58:00 EST') as numhours;
numhours
----------
1
```
# DATE\$1PART function
DATE\$1PART extracts date part values from an expression. DATE\$1PART is a synonym of the PGDATE\$1PART function.
## Syntax
```
DATE_PART(datepart, {date|timestamp})
```
## Arguments
*datepart*
An identifier literal or string of the specific part of the date value (for example, year, month, or day) that the function operates on. For more information, see [Date parts for date or timestamp functions](r_Dateparts_for_datetime_functions.md).
\$1*date*\$1*timestamp*\$1
A date column, timestamp column, or an expression that implicitly converts to a date or timestamp. The column or expression in *date* or *timestamp* must contain the date part specified in *datepart*.
## Return type
DOUBLE
## Examples
The default column name for the DATE\$1PART function is `pgdate_part`.
For more information about the data used in some of these examples, see [Sample database](c_sampledb.md).
The following example finds the minute from a timestamp literal.
```
SELECT DATE_PART(minute, timestamp '20230104 04:05:06.789');
pgdate_part
-----------
5
```
The following example finds the week number from a timestamp literal. The week number calculation follows the ISO 8601 standard. For more information, see [ISO 8601](https://en.wikipedia.org/wiki/ISO_8601) in Wikipedia.
```
SELECT DATE_PART(week, timestamp '20220502 04:05:06.789');
pgdate_part
-----------
18
```
The following example finds the day of the month from a timestamp literal.
```
SELECT DATE_PART(day, timestamp '20220502 04:05:06.789');
pgdate_part
-----------
2
```
The following example finds the day of the week from a timestamp literal. The day of week number calculation is an integer from 0-6, starting with Sunday.
```
SELECT DATE_PART(dayofweek, timestamp '20220502 04:05:06.789');
pgdate_part
-----------
1
```
The following example finds the century from a timestamp literal. The century calculation follows the ISO 8601 standard. For more information, see [ISO 8601](https://en.wikipedia.org/wiki/ISO_8601) in Wikipedia.
```
SELECT DATE_PART(century, timestamp '20220502 04:05:06.789');
pgdate_part
-----------
21
```
The following example finds the millennium from a timestamp literal. The millennium calculation follows the ISO 8601 standard. For more information, see [ISO 8601](https://en.wikipedia.org/wiki/ISO_8601) in Wikipedia.
```
SELECT DATE_PART(millennium, timestamp '20220502 04:05:06.789');
pgdate_part
-----------
3
```
The following example finds the microseconds from a timestamp literal. The microseconds calculation follows the ISO 8601 standard. For more information, see [ISO 8601](https://en.wikipedia.org/wiki/ISO_8601) in Wikipedia.
```
SELECT DATE_PART(microsecond, timestamp '20220502 04:05:06.789');
pgdate_part
-----------
789000
```
The following example finds the month from a date literal.
```
SELECT DATE_PART(month, date '20220502');
pgdate_part
-----------
5
```
The following example applies the DATE\$1PART function to a column in a table.
```
SELECT date_part(w, listtime) AS weeks, listtime
FROM listing
WHERE listid=10
weeks | listtime
------+---------------------
25 | 2008-06-17 09:44:54
(1 row)
```
You can name date parts in full or abbreviate them; in this case, *w* stands for weeks.
The day of week date part returns an integer from 0-6, starting with Sunday. Use DATE\$1PART with dow (DAYOFWEEK) to view events on a Saturday.
```
SELECT date_part(dow, starttime) AS dow, starttime
FROM event
WHERE date_part(dow, starttime)=6
ORDER BY 2,1;
dow | starttime
-----+---------------------
6 | 2008-01-05 14:00:00
6 | 2008-01-05 14:00:00
6 | 2008-01-05 14:00:00
6 | 2008-01-05 14:00:00
...
(1147 rows)
```
# DATE\$1PART\$1YEAR function
The DATE\$1PART\$1YEAR function extracts the year from a date.
## Syntax
```
DATE_PART_YEAR(date)
```
## Argument
*date*
A column of data type `DATE` or an expression that implicitly evaluates to a `DATE` type.
## Return type
INTEGER
## Examples
The following example finds the year from a date literal.
```
SELECT DATE_PART_YEAR(date '20220502 04:05:06.789');
date_part_year
---------------
2022
```
The following example extracts the year from the CALDATE column. The values in the CALDATE column are dates. For more information about the data used in this example, see [Sample database](c_sampledb.md).
```
select caldate, date_part_year(caldate)
from date
order by
dateid limit 10;
caldate | date_part_year
-----------+----------------
2008-01-01 | 2008
2008-01-02 | 2008
2008-01-03 | 2008
2008-01-04 | 2008
2008-01-05 | 2008
2008-01-06 | 2008
2008-01-07 | 2008
2008-01-08 | 2008
2008-01-09 | 2008
2008-01-10 | 2008
(10 rows)
```
# DATE\$1TRUNC function
The DATE\$1TRUNC function truncates a timestamp expression or literal based on the date part that you specify, such as hour, day, or month.
## Syntax
```
DATE_TRUNC('datepart', timestamp)
```
## Arguments
*datepart*
The date part to which to truncate the timestamp value. The input *timestamp* is truncated to the precision of the input *datepart*. For example, `month` truncates to the first day of the month. Valid formats are as follows:
+ microsecond, microseconds
+ millisecond, milliseconds
+ second, seconds
+ minute, minutes
+ hour, hours
+ day, days
+ week, weeks
+ month, months
+ quarter, quarters
+ year, years
+ decade, decades
+ century, centuries
+ millennium, millennia
For more information about abbreviations of some formats, see [Date parts for date or timestamp functions](r_Dateparts_for_datetime_functions.md)
*timestamp*
A timestamp column or an expression that implicitly converts to a timestamp.
## Return type
TIMESTAMP
## Examples
Truncate the input timestamp to the second.
```
SELECT DATE_TRUNC('second', TIMESTAMP '20200430 04:05:06.789');
date_trunc
2020-04-30 04:05:06
```
Truncate the input timestamp to the minute.
```
SELECT DATE_TRUNC('minute', TIMESTAMP '20200430 04:05:06.789');
date_trunc
2020-04-30 04:05:00
```
Truncate the input timestamp to the hour.
```
SELECT DATE_TRUNC('hour', TIMESTAMP '20200430 04:05:06.789');
date_trunc
2020-04-30 04:00:00
```
Truncate the input timestamp to the day.
```
SELECT DATE_TRUNC('day', TIMESTAMP '20200430 04:05:06.789');
date_trunc
2020-04-30 00:00:00
```
Truncate the input timestamp to the first day of a month.
```
SELECT DATE_TRUNC('month', TIMESTAMP '20200430 04:05:06.789');
date_trunc
2020-04-01 00:00:00
```
Truncate the input timestamp to the first day of a quarter.
```
SELECT DATE_TRUNC('quarter', TIMESTAMP '20200430 04:05:06.789');
date_trunc
2020-04-01 00:00:00
```
Truncate the input timestamp to the first day of a year.
```
SELECT DATE_TRUNC('year', TIMESTAMP '20200430 04:05:06.789');
date_trunc
2020-01-01 00:00:00
```
Truncate the input timestamp to the first day of a century.
```
SELECT DATE_TRUNC('millennium', TIMESTAMP '20200430 04:05:06.789');
date_trunc
2001-01-01 00:00:00
```
Truncate the input timestamp to the Monday of a week.
```
select date_trunc('week', TIMESTAMP '20220430 04:05:06.789');
date_trunc
2022-04-25 00:00:00
```
In the following example, the DATE\$1TRUNC function uses the 'week' date part to return the date for the Monday of each week.
```
select date_trunc('week', saletime), sum(pricepaid) from sales where
saletime like '2008-09%' group by date_trunc('week', saletime) order by 1;
date_trunc | sum
------------+-------------
2008-09-01 | 2474899
2008-09-08 | 2412354
2008-09-15 | 2364707
2008-09-22 | 2359351
2008-09-29 | 705249
```
# EXTRACT function
The EXTRACT function returns a date or time part from a TIMESTAMP, TIMESTAMPTZ, TIME, TIMETZ, INTERVAL YEAR TO MONTH, or INTERVAL DAY TO SECOND value. Examples include a day, month, year, hour, minute, second, millisecond, or microsecond from a timestamp.
## Syntax
```
EXTRACT(datepart FROM source)
```
## Arguments
*datepart*
The subfield of a date or time to extract, such as a day, month, year, hour, minute, second, millisecond, or microsecond. For possible values, see [Date parts for date or timestamp functions](r_Dateparts_for_datetime_functions.md).
*source*
A column or expression that evaluates to a data type of TIMESTAMP, TIMESTAMPTZ, TIME, TIMETZ, INTERVAL YEAR TO MONTH, or INTERVAL DAY TO SECOND.
## Return type
INTEGER if the *source* value evaluates to data type TIMESTAMP, TIME, TIMETZ, INTERVAL YEAR TO MONTH, or INTERVAL DAY TO SECOND.
DOUBLE PRECISION if the *source* value evaluates to data type TIMESTAMPTZ.
## Examples with TIMESTAMP
The following example determines the week numbers for sales in which the price paid was \$110,000 or more. This example uses the TICKIT data. For more information, see [Sample database](c_sampledb.md).
```
select salesid, extract(week from saletime) as weeknum
from sales
where pricepaid > 9999
order by 2;
salesid | weeknum
--------+---------
159073 | 6
160318 | 8
161723 | 26
```
The following example returns the minute value from a literal timestamp value.
```
select extract(minute from timestamp '2009-09-09 12:08:43');
date_part
-----------
8
```
The following example returns the millisecond value from a literal timestamp value.
```
select extract(ms from timestamp '2009-09-09 12:08:43.101');
date_part
-----------
101
```
## Examples with TIMESTAMPTZ
The following example returns the year value from a literal timestamptz value.
```
select extract(year from timestamptz '1.12.1997 07:37:16.00 PST');
date_part
-----------
1997
```
## Examples with TIME
The following example table TIME\$1TEST has a column TIME\$1VAL (type TIME) with three values inserted.
```
select time_val from time_test;
time_val
---------------------
20:00:00
00:00:00.5550
00:58:00
```
The following example extracts the minutes from each time\$1val.
```
select extract(minute from time_val) as minutes from time_test;
minutes
-----------
0
0
58
```
The following example extracts the hours from each time\$1val.
```
select extract(hour from time_val) as hours from time_test;
hours
-----------
20
0
0
```
The following example extracts milliseconds from a literal value.
```
select extract(ms from time '18:25:33.123456');
date_part
-----------
123
```
## Examples with TIMETZ
The following example table TIMETZ\$1TEST has a column TIMETZ\$1VAL (type TIMETZ) with three values inserted.
```
select timetz_val from timetz_test;
timetz_val
------------------
04:00:00+00
00:00:00.5550+00
05:58:00+00
```
The following example extracts the hours from each timetz\$1val.
```
select extract(hour from timetz_val) as hours from time_test;
hours
-----------
4
0
5
```
The following example extracts milliseconds from a literal value. Literals aren't converted to UTC before the extraction is processed.
```
select extract(ms from timetz '18:25:33.123456 EST');
date_part
-----------
123
```
The following example returns the timezone offset hour from UTC from a literal timetz value.
```
select extract(timezone_hour from timetz '1.12.1997 07:37:16.00 PDT');
date_part
-----------
-7
```
## Examples with INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND
The following example extracts the day part of `1` from the INTERVAL DAY TO SECOND that defines 36 hours, which is 1 day 12 hours.
```
select EXTRACT('days' from INTERVAL '36 hours' DAY TO SECOND)
date_part
------------------
1
```
The following example extracts the month part of `3` from the YEAR TO MONTH that defines 15 months, which is 1 year 3 months.
```
select EXTRACT('month' from INTERVAL '15 months' YEAR TO MONTH)
date_part
------------------
3
```
The following example extracts the month part of `6` from 30 months which is 2 years 6 months.
```
select EXTRACT('month' from INTERVAL '30' MONTH)
date_part
------------------
6
```
The following example extracts the hour part of `2` from 50 hours which is 2 days 2 hours.
```
select EXTRACT('hours' from INTERVAL '50' HOUR)
date_part
------------------
2
```
The following example extracts the minute part of `11` from 1 hour 11 minutes 11.123 seconds.
```
select EXTRACT('minute' from INTERVAL '70 minutes 70.123 seconds' MINUTE TO SECOND)
date_part
------------------
11
```
The following example extracts the seconds part of `1.11` from 1 day 1 hour 1 minute 1.11 seconds.
```
select EXTRACT('seconds' from INTERVAL '1 day 1:1:1.11' DAY TO SECOND)
date_part
------------------
1.11
```
The following example extracts the total number of hours in an INTERVAL. Each part is extracted and added to a total.
```
select EXTRACT('days' from INTERVAL '50' HOUR) * 24 + EXTRACT('hours' from INTERVAL '50' HOUR)
?column?
------------------
50
```
The following example extracts the total number of seconds in an INTERVAL. Each part is extracted and added to a total.
```
select EXTRACT('days' from INTERVAL '1 day 1:1:1.11' DAY TO SECOND) * 86400 +
EXTRACT('hours' from INTERVAL '1 day 1:1:1.11' DAY TO SECOND) * 3600 +
EXTRACT('minutes' from INTERVAL '1 day 1:1:1.11' DAY TO SECOND) * 60 +
EXTRACT('seconds' from INTERVAL '1 day 1:1:1.11' DAY TO SECOND)
?column?
------------------
90061.11
```
# GETDATE function
GETDATE returns the current date and time in the current session time zone (UTC by default). It returns the start date or time of the current statement, even when it is within a transaction block.
## Syntax
```
GETDATE()
```
The parentheses are required.
## Return type
TIMESTAMP
## Examples
The following example uses the GETDATE function to return the full timestamp for the current date.
```
select getdate();
timestamp
---------------------
2008-12-04 16:10:43
```
The following example uses the GETDATE function inside the TRUNC function to return the current date without the time.
```
select trunc(getdate());
trunc
------------
2008-12-04
```
# INTERVAL\$1CMP function
INTERVAL\$1CMP compares two intervals and returns `1` if the first interval is greater, `-1` if the second interval is greater, and `0` if the intervals are equal. For more information, see [Examples of interval literals without qualifier syntax](r_interval_literals.md).
## Syntax
```
INTERVAL_CMP(interval1, interval2)
```
## Arguments
*interval1*
An interval literal value.
*interval2*
An interval literal value.
## Return type
INTEGER
## Examples
The following example compares the value of `3 days` to `1 year`.
```
select interval_cmp('3 days','1 year');
interval_cmp
--------------
-1
```
This example compares the value `7 days` to `1 week`.
```
select interval_cmp('7 days','1 week');
interval_cmp
--------------
0
```
The following example compares the value of `1 year` to `3 days`.
```
select interval_cmp('1 year','3 days');
interval_cmp
--------------
1
```
# LAST\$1DAY function
LAST\$1DAY returns the date of the last day of the month that contains *date*. The return type is always DATE, regardless of the data type of the *date* argument.
For more information about retrieving specific date parts, see [DATE\$1TRUNC function](r_DATE_TRUNC.md).
## Syntax
```
LAST_DAY( { date | timestamp } )
```
## Arguments
*date* \$1 *timestamp*
A column of data type `DATE` or `TIMESTAMP` or an expression that implicitly evaluates to a `DATE` or `TIMESTAMP` type.
## Return type
DATE
## Examples
The following example returns the date of the last day in the current month.
```
select last_day(sysdate);
last_day
------------
2014-01-31
```
The following example returns the number of tickets sold for each of the last 7 days of the month. The values in the SALETIME column are timestamps.
```
select datediff(day, saletime, last_day(saletime)) as "Days Remaining", sum(qtysold)
from sales
where datediff(day, saletime, last_day(saletime)) < 7
group by 1
order by 1;
days remaining | sum
---------------+-------
0 | 10140
1 | 11187
2 | 11515
3 | 11217
4 | 11446
5 | 11708
6 | 10988
(7 rows)
```
# MONTHS\$1BETWEEN function
MONTHS\$1BETWEEN determines the number of months between two dates.
If the first date is later than the second date, the result is positive; otherwise, the result is negative.
If either argument is null, the result is NULL.
## Syntax
```
MONTHS_BETWEEN( date1, date2 )
```
## Arguments
*date1*
A column of data type `DATE` or an expression that implicitly evaluates to a `DATE` type.
*date2*
A column of data type `DATE` or an expression that implicitly evaluates to a `DATE` type.
## Return type
FLOAT8
The whole number portion of the result is based on the difference between the year and month values of the dates. The fractional portion of the result is calculated from the day and timestamp values of the dates and assumes a 31-day month.
If *date1* and *date2* both contain the same date within a month (for example, 1/15/14 and 2/15/14) or the last day of the month (for example, 8/31/14 and 9/30/14), then the result is a whole number based on the year and month values of the dates, regardless of whether the timestamp portion matches, if present.
## Examples
The following example returns the months between 1/18/1969 and 3/18/1969.
```
select months_between('1969-01-18', '1969-03-18')
as months;
months
----------
-2
```
The following example returns the months between 1/18/1969 and 1/18/1969.
```
select months_between('1969-01-18', '1969-01-18')
as months;
months
----------
0
```
The following example returns the months between the first and last showings of an event.
```
select eventname,
min(starttime) as first_show,
max(starttime) as last_show,
months_between(max(starttime),min(starttime)) as month_diff
from event
group by eventname
order by eventname
limit 5;
eventname first_show last_show month_diff
---------------------------------------------------------------------------
.38 Special 2008-01-21 19:30:00.0 2008-12-25 15:00:00.0 11.12
3 Doors Down 2008-01-03 15:00:00.0 2008-12-01 19:30:00.0 10.94
70s Soul Jam 2008-01-16 19:30:00.0 2008-12-07 14:00:00.0 10.7
A Bronx Tale 2008-01-21 19:00:00.0 2008-12-15 15:00:00.0 10.8
A Catered Affair 2008-01-08 19:30:00.0 2008-12-19 19:00:00.0 11.35
```
# NEXT\$1DAY function
NEXT\$1DAY returns the date of the first instance of the specified day that is later than the given date.
If the *day* value is the same day of the week as the given date, the next occurrence of that day is returned.
## Syntax
```
NEXT_DAY( { date | timestamp }, day )
```
## Arguments
*date* \$1 *timestamp*
A column of data type `DATE` or `TIMESTAMP` or an expression that implicitly evaluates to a `DATE` or `TIMESTAMP` type.
*day*
A string containing the name of any day. Capitalization doesn't matter.
Valid values are as follows.
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_NEXT_DAY.html)
## Return type
DATE
## Examples
The following example returns the date of the first Tuesday after 8/20/2014.
```
select next_day('2014-08-20','Tuesday');
next_day
-----------
2014-08-26
```
The following example returns the date of the first Tuesday after 1/1/2008 at 5:54:44.
```
select listtime, next_day(listtime, 'Tue') from listing limit 1;
listtime | next_day
--------------------+-----------
2008-01-01 05:54:44 | 2008-01-08
```
The following example gets target marketing dates for the third quarter.
```
select username, (firstname ||' '|| lastname) as name,
eventname, caldate, next_day (caldate, 'Monday') as marketing_target
from sales, date, users, event
where sales.buyerid = users.userid
and sales.eventid = event.eventid
and event.dateid = date.dateid
and date.qtr = 3
order by marketing_target, eventname, name;
username | name | eventname | caldate | marketing_target
----------+-------------------+----------------------+--------------+-------------------
MBO26QSG | Callum Atkinson | .38 Special | 2008-07-06 | 2008-07-07
WCR50YIU | Erasmus Alvarez | A Doll's House | 2008-07-03 | 2008-07-07
CKT70OIE | Hadassah Adkins | Ana Gabriel | 2008-07-06 | 2008-07-07
VVG07OUO | Nathan Abbott | Armando Manzanero | 2008-07-04 | 2008-07-07
GEW77SII | Scarlet Avila | August: Osage County | 2008-07-06 | 2008-07-07
ECR71CVS | Caryn Adkins | Ben Folds | 2008-07-03 | 2008-07-07
KUW82CYU | Kaden Aguilar | Bette Midler | 2008-07-01 | 2008-07-07
WZE78DJZ | Kay Avila | Bette Midler | 2008-07-01 | 2008-07-07
HXY04NVE | Dante Austin | Britney Spears | 2008-07-02 | 2008-07-07
URY81YWF | Wilma Anthony | Britney Spears | 2008-07-02 | 2008-07-07
```
# SYSDATE function
SYSDATE returns the current date and time in the current session time zone (UTC by default).
**Note**
SYSDATE returns the start date and time for the current transaction, not for the start of the current statement.
## Syntax
```
SYSDATE
```
This function requires no arguments.
## Return type
TIMESTAMP
## Examples
The following example uses the SYSDATE function to return the full timestamp for the current date.
```
select sysdate;
timestamp
----------------------------
2008-12-04 16:10:43.976353
```
The following example uses the SYSDATE function inside the TRUNC function to return the current date without the time.
```
select trunc(sysdate);
trunc
------------
2008-12-04
```
The following query returns sales information for dates that fall between the date when the query is issued and whatever date is 120 days earlier.
```
select salesid, pricepaid, trunc(saletime) as saletime, trunc(sysdate) as now
from sales
where saletime between trunc(sysdate)-120 and trunc(sysdate)
order by saletime asc;
salesid | pricepaid | saletime | now
---------+-----------+------------+------------
91535 | 670.00 | 2008-08-07 | 2008-12-05
91635 | 365.00 | 2008-08-07 | 2008-12-05
91901 | 1002.00 | 2008-08-07 | 2008-12-05
...
```
# TIMEOFDAY function
TIMEOFDAY is a special alias used to return the weekday, date, and time as a string value. It returns the time of day string for the current statement, even when it is within a transaction block.
## Syntax
```
TIMEOFDAY()
```
## Return type
VARCHAR
## Examples
The following example returns the current date and time by using the TIMEOFDAY function.
```
select timeofday();
timeofday
------------
Thu Sep 19 22:53:50.333525 2013 UTC
```
# TIMESTAMP\$1CMP function
Compares the value of two timestamps and returns an integer. If the timestamps are identical, the function returns `0`. If the first timestamp is greater, the function returns `1`. If the second timestamp is greater, the function returns `-1`.
## Syntax
```
TIMESTAMP_CMP(timestamp1, timestamp2)
```
## Arguments
*timestamp1*
A column of data type `TIMESTAMP` or an expression that implicitly evaluates to a `TIMESTAMP` type.
*timestamp2*
A column of data type `TIMESTAMP` or an expression that implicitly evaluates to a `TIMESTAMP` type.
## Return type
INTEGER
## Examples
The following example compares timestamps and shows the results of the comparison.
```
SELECT TIMESTAMP_CMP('2008-01-24 06:43:29', '2008-01-24 06:43:29'), TIMESTAMP_CMP('2008-01-24 06:43:29', '2008-02-18 02:36:48'), TIMESTAMP_CMP('2008-02-18 02:36:48', '2008-01-24 06:43:29');
timestamp_cmp | timestamp_cmp | timestamp_cmp
---------------+---------------+---------------
0 | -1 | 1
```
The following example compares the LISTTIME and SALETIME for a listing. The value for TIMESTAMP\$1CMP is `-1` for all listings because the timestamp for the sale is after the timestamp for the listing.
```
select listing.listid, listing.listtime,
sales.saletime, timestamp_cmp(listing.listtime, sales.saletime)
from listing, sales
where listing.listid=sales.listid
order by 1, 2, 3, 4
limit 10;
listid | listtime | saletime | timestamp_cmp
--------+---------------------+---------------------+---------------
1 | 2008-01-24 06:43:29 | 2008-02-18 02:36:48 | -1
4 | 2008-05-24 01:18:37 | 2008-06-06 05:00:16 | -1
5 | 2008-05-17 02:29:11 | 2008-06-06 08:26:17 | -1
5 | 2008-05-17 02:29:11 | 2008-06-09 08:38:52 | -1
6 | 2008-08-15 02:08:13 | 2008-08-31 09:17:02 | -1
10 | 2008-06-17 09:44:54 | 2008-06-26 12:56:06 | -1
10 | 2008-06-17 09:44:54 | 2008-07-10 02:12:36 | -1
10 | 2008-06-17 09:44:54 | 2008-07-16 11:59:24 | -1
10 | 2008-06-17 09:44:54 | 2008-07-22 02:23:17 | -1
12 | 2008-07-25 01:45:49 | 2008-08-04 03:06:36 | -1
(10 rows)
```
This example shows that TIMESTAMP\$1CMP returns a 0 for identical timestamps:
```
select listid, timestamp_cmp(listtime, listtime)
from listing
order by 1 , 2
limit 10;
listid | timestamp_cmp
--------+---------------
1 | 0
2 | 0
3 | 0
4 | 0
5 | 0
6 | 0
7 | 0
8 | 0
9 | 0
10 | 0
(10 rows)
```
# TIMESTAMP\$1CMP\$1DATE function
TIMESTAMP\$1CMP\$1DATE compares the value of a timestamp and a date. If the timestamp and date values are identical, the function returns `0`. If the timestamp is greater chronologically, the function returns `1`. If the date is greater, the function returns `-1`.
## Syntax
```
TIMESTAMP_CMP_DATE(timestamp, date)
```
## Arguments
*timestamp*
A column of data type `TIMESTAMP` or an expression that implicitly evaluates to a `TIMESTAMP` type.
*date*
A column of data type `DATE` or an expression that implicitly evaluates to a `DATE` type.
## Return type
INTEGER
## Examples
The following example compares LISTTIME to the date `2008-06-18`. Listings made after this date return `1`; listings made before this date return `-1`. LISTTIME values are timestamps.
```
select listid, listtime,
timestamp_cmp_date(listtime, '2008-06-18')
from listing
order by 1, 2, 3
limit 10;
listid | listtime | timestamp_cmp_date
--------+---------------------+--------------------
1 | 2008-01-24 06:43:29 | -1
2 | 2008-03-05 12:25:29 | -1
3 | 2008-11-01 07:35:33 | 1
4 | 2008-05-24 01:18:37 | -1
5 | 2008-05-17 02:29:11 | -1
6 | 2008-08-15 02:08:13 | 1
7 | 2008-11-15 09:38:15 | 1
8 | 2008-11-09 05:07:30 | 1
9 | 2008-09-09 08:03:36 | 1
10 | 2008-06-17 09:44:54 | -1
(10 rows)
```
# TIMESTAMP\$1CMP\$1TIMESTAMPTZ function
TIMESTAMP\$1CMP\$1TIMESTAMPTZ compares the value of a timestamp expression with a timestamp with time zone expression. If the timestamp and timestamp with time zone values are identical, the function returns `0`. If the timestamp is greater chronologically, the function returns `1`. If the timestamp with time zone is greater, the function returns `–1`.
## Syntax
```
TIMESTAMP_CMP_TIMESTAMPTZ(timestamp, timestamptz)
```
## Arguments
*timestamp*
A column of data type `TIMESTAMP` or an expression that implicitly evaluates to a `TIMESTAMP` type.
*timestamptz*
A column of data type `TIMESTAMPTZ` or an expression that implicitly evaluates to a `TIMESTAMPTZ` type.
## Return type
INTEGER
## Examples
The following example compares timestamps to timestamps with time zones and shows the results of the comparison.
```
SELECT TIMESTAMP_CMP_TIMESTAMPTZ('2008-01-24 06:43:29', '2008-01-24 06:43:29+00'), TIMESTAMP_CMP_TIMESTAMPTZ('2008-01-24 06:43:29', '2008-02-18 02:36:48+00'), TIMESTAMP_CMP_TIMESTAMPTZ('2008-02-18 02:36:48', '2008-01-24 06:43:29+00');
timestamp_cmp_timestamptz | timestamp_cmp_timestamptz | timestamp_cmp_timestamptz
---------------------------+---------------------------+--------------------------
0 | -1 | 1
```
# TIMESTAMPTZ\$1CMP function
TIMESTAMPTZ\$1CMP compares the value of two timestamp with time zone values and returns an integer. If the timestamps are identical, the function returns `0`. If the first timestamp is greater chronologically, the function returns `1`. If the second timestamp is greater, the function returns `–1`.
## Syntax
```
TIMESTAMPTZ_CMP(timestamptz1, timestamptz2)
```
## Arguments
*timestamptz1*
A column of data type `TIMESTAMPTZ` or an expression that implicitly evaluates to a `TIMESTAMPTZ` type.
*timestamptz2*
A column of data type `TIMESTAMPTZ` or an expression that implicitly evaluates to a `TIMESTAMPTZ` type.
## Return type
INTEGER
## Examples
The following example compares timestamps with time zones and shows the results of the comparison.
```
SELECT TIMESTAMPTZ_CMP('2008-01-24 06:43:29+00', '2008-01-24 06:43:29+00'), TIMESTAMPTZ_CMP('2008-01-24 06:43:29+00', '2008-02-18 02:36:48+00'), TIMESTAMPTZ_CMP('2008-02-18 02:36:48+00', '2008-01-24 06:43:29+00');
timestamptz_cmp | timestamptz_cmp | timestamptz_cmp
-----------------+-----------------+----------------
0 | -1 | 1
```
# TIMESTAMPTZ\$1CMP\$1DATE function
TIMESTAMPTZ\$1CMP\$1DATE compares the value of a timestamp and a date. If the timestamp and date values are identical, the function returns `0`. If the timestamp is greater chronologically, the function returns `1`. If the date is greater, the function returns `–1`.
## Syntax
```
TIMESTAMPTZ_CMP_DATE(timestamptz, date)
```
## Arguments
*timestamptz*
A column of data type `TIMESTAMPTZ` or an expression that implicitly evaluates to a `TIMESTAMPTZ` type.
*date*
A column of data type `DATE` or an expression that implicitly evaluates to a `DATE` type.
## Return type
INTEGER
## Examples
The following example compares LISTTIME as a timestamp with time zone to the date `2008-06-18`. Listings made after this date return `1`; listings made before this date return `-1`.
```
select listid, CAST(listtime as timestamptz) as tstz,
timestamp_cmp_date(tstz, '2008-06-18')
from listing
order by 1, 2, 3
limit 10;
listid | tstz | timestamptz_cmp_date
--------+------------------------+----------------------
1 | 2008-01-24 06:43:29+00 | -1
2 | 2008-03-05 12:25:29+00 | -1
3 | 2008-11-01 07:35:33+00 | 1
4 | 2008-05-24 01:18:37+00 | -1
5 | 2008-05-17 02:29:11+00 | -1
6 | 2008-08-15 02:08:13+00 | 1
7 | 2008-11-15 09:38:15+00 | 1
8 | 2008-11-09 05:07:30+00 | 1
9 | 2008-09-09 08:03:36+00 | 1
10 | 2008-06-17 09:44:54+00 | -1
(10 rows)
```
# TIMESTAMPTZ\$1CMP\$1TIMESTAMP function
TIMESTAMPTZ\$1CMP\$1TIMESTAMP compares the value of a timestamp with time zone expression with a timestamp expression. If the timestamp with time zone and timestamp values are identical, the function returns `0`. If the timestamp with time zone is greater chronologically, the function returns `1`. If the timestamp is greater, the function returns `–1`.
## Syntax
```
TIMESTAMPTZ_CMP_TIMESTAMP(timestamptz, timestamp)
```
## Arguments
*timestamptz*
A column of data type `TIMESTAMPTZ` or an expression that implicitly evaluates to a `TIMESTAMPTZ` type.
*timestamp*
A column of data type `TIMESTAMP` or an expression that implicitly evaluates to a `TIMESTAMP` type.
## Return type
INTEGER
## Examples
The following example compares timestamps with time zones to timestamps and shows the results of the comparison.
```
SELECT TIMESTAMPTZ_CMP_TIMESTAMP('2008-01-24 06:43:29+00', '2008-01-24 06:43:29'), TIMESTAMPTZ_CMP_TIMESTAMP('2008-01-24 06:43:29+00', '2008-02-18 02:36:48'), TIMESTAMPTZ_CMP_TIMESTAMP('2008-02-18 02:36:48+00', '2008-01-24 06:43:29');
timestamptz_cmp_timestamp | timestamptz_cmp_timestamp | timestamptz_cmp_timestamp
---------------------------+---------------------------+---------------------------
0 | -1 | 1
```
# TIMEZONE function
TIMEZONE returns a timestamp for the specified time zone and timestamp value.
For information and examples about how to set time zone, see [timezone](r_timezone_config.md).
For information and examples about how to convert time zone, see [CONVERT\$1TIMEZONE](CONVERT_TIMEZONE.md).
## Syntax
```
TIMEZONE('timezone', { timestamp | timestamptz })
```
## Arguments
*timezone*
The time zone for the return value. The time zone can be specified as a time zone name (such as **'Africa/Kampala'** or **'Singapore'**) or as a time zone abbreviation (such as **'UTC'** or **'PDT'**). To view a list of supported time zone names, run the following command.
```
select pg_timezone_names();
```
To view a list of supported time zone abbreviations, run the following command.
```
select pg_timezone_abbrevs();
```
Note that Amazon Redshift uses the [IANA Time Zone Database](https://www.iana.org/time-zones) as the authoritative source of time zone specification. For more information and examples, see [Time zone usage notes](CONVERT_TIMEZONE.md#CONVERT_TIMEZONE-usage-notes).
*timestamp* \$1 *timestamptz*
An expression that results in a TIMESTAMP or TIMESTAMPTZ type, or a value that can implicitly be coerced to a timestamp or a timestamp with time zone.
## Return type
TIMESTAMPTZ when used with a TIMESTAMP expression.
TIMESTAMP when used with a TIMESTAMPTZ expression.
## Examples
The following returns a timestamp for the UTC time zone using the timestamp `2008-06-17 09:44:54` from the PST timezone.
```
SELECT TIMEZONE('PST', '2008-06-17 09:44:54');
timezone
-----------------------
2008-06-17 17:44:54+00
```
The following returns a timestamp for the PST time zone using the timestamp with UTC time zone `2008-06-17 09:44:54+00`.
```
SELECT TIMEZONE('PST', timestamptz('2008-06-17 09:44:54+00'));
timezone
-----------------------
2008-06-17 01:44:54
```
# TO\$1TIMESTAMP function
TO\$1TIMESTAMP converts a TIMESTAMP string to TIMESTAMPTZ. For a list of additional date and time functions for Amazon Redshift, see [Date and time functions](Date_functions_header.md).
## Syntax
```
to_timestamp(timestamp, format)
```
```
to_timestamp (timestamp, format, is_strict)
```
## Arguments
*timestamp*
A string that represents a timestamp value in the format specified by *format*. If this argument is left as empty, the timestamp value defaults to `0001-01-01 00:00:00`.
*format*
A string literal that defines the format of the *timestamp* value. Formats that include a time zone (**TZ**, **tz**, or **OF**) are not supported as input. For valid timestamp formats, see [Datetime format strings](r_FORMAT_strings.md).
*is\$1strict*
An optional Boolean value that specifies whether an error is returned if an input timestamp value is out of range. When *is\$1strict* is set to TRUE, an error is returned if there is an out of range value. When *is\$1strict* is set to FALSE, which is the default, then overflow values are accepted.
## Return type
TIMESTAMPTZ
## Examples
The following example demonstrates using the TO\$1TIMESTAMP function to convert a TIMESTAMP string to a TIMESTAMPTZ.
```
select sysdate, to_timestamp(sysdate, 'YYYY-MM-DD HH24:MI:SS') as second;
timestamp | second
-------------------------- ----------------------
2021-04-05 19:27:53.281812 | 2021-04-05 19:27:53+00
```
It's possible to pass TO\$1TIMESTAMP part of a date. The remaining date parts are set to default values. The time is included in the output:
```
SELECT TO_TIMESTAMP('2017','YYYY');
to_timestamp
--------------------------
2017-01-01 00:00:00+00
```
The following SQL statement converts the string '2011-12-18 24:38:15' to a TIMESTAMPTZ. The result is a TIMESTAMPTZ that falls on the next day because the number of hours is more than 24 hours:
```
SELECT TO_TIMESTAMP('2011-12-18 24:38:15', 'YYYY-MM-DD HH24:MI:SS');
to_timestamp
----------------------
2011-12-19 00:38:15+00
```
The following SQL statement converts the string '2011-12-18 24:38:15' to a TIMESTAMPTZ. The result is an error because the time value in the timestamp is more than 24 hours:
```
SELECT TO_TIMESTAMP('2011-12-18 24:38:15', 'YYYY-MM-DD HH24:MI:SS', TRUE);
ERROR: date/time field time value out of range: 24:38:15.0
```
# TRUNC function
Truncates a `TIMESTAMP` and returns a `DATE`.
This function can also truncate a number. For more information, see [TRUNC function](r_TRUNC.md).
## Syntax
```
TRUNC(timestamp)
```
## Arguments
*timestamp*
A column of data type `TIMESTAMP` or an expression that implicitly evaluates to a `TIMESTAMP` type.
To return a timestamp value with `00:00:00` as the time, cast the function result to a `TIMESTAMP`.
## Return type
DATE
## Examples
The following example returns the date portion from the result of the SYSDATE function (which returns a timestamp).
```
SELECT SYSDATE;
+----------------------------+
| timestamp |
+----------------------------+
| 2011-07-21 10:32:38.248109 |
+----------------------------+
SELECT TRUNC(SYSDATE);
+------------+
| trunc |
+------------+
| 2011-07-21 |
+------------+
```
The following example applies the TRUNC function to a `TIMESTAMP` column. The return type is a date.
```
SELECT TRUNC(starttime) FROM event
ORDER BY eventid LIMIT 1;
+------------+
| trunc |
+------------+
| 2008-01-25 |
+------------+
```
The following example returns a timestamp value with `00:00:00` as the time by casting the TRUNC function result to a `TIMESTAMP`.
```
SELECT CAST((TRUNC(SYSDATE)) AS TIMESTAMP);
+---------------------+
| trunc |
+---------------------+
| 2011-07-21 00:00:00 |
+---------------------+
```
# Date parts for date or timestamp functions
The following table identifies the date part and time part names and abbreviations that are accepted as arguments to the following functions:
+ DATEADD
+ DATEDIFF
+ DATE\$1PART
+ EXTRACT
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_Dateparts_for_datetime_functions.html)
## Variations in results with seconds, milliseconds, and microseconds
Minor differences in query results occur when different date functions specify seconds, milliseconds, or microseconds as date parts:
+ The EXTRACT function return integers for the specified date part only, ignoring higher- and lower-level date parts. If the specified date part is seconds, milliseconds and microseconds are not included in the result. If the specified date part is milliseconds, seconds and microseconds are not included. If the specified date part is microseconds, seconds and milliseconds are not included.
+ The DATE\$1PART function returns the complete seconds portion of the timestamp, regardless of the specified date part, returning either a decimal value or an integer as required.
For example, compare the results of the following queries:
```
create table seconds(micro timestamp);
insert into seconds values('2009-09-21 11:10:03.189717');
select extract(sec from micro) from seconds;
date_part
-----------
3
select date_part(sec, micro) from seconds;
pgdate_part
-------------
3.189717
```
## CENTURY, EPOCH, DECADE, and MIL notes
CENTURY or CENTURIES
Amazon Redshift interprets a CENTURY to start with year *\$1\$1\$11* and end with year `###0`:
```
select extract (century from timestamp '2000-12-16 12:21:13');
date_part
-----------
20
select extract (century from timestamp '2001-12-16 12:21:13');
date_part
-----------
21
```
EPOCH
The Amazon Redshift implementation of EPOCH is relative to 1970-01-01 00:00:00.000000 independent of the time zone where the cluster resides. You might need to offset the results by the difference in hours depending on the time zone where the cluster is located.
The following example demonstrates the following:
1. Creates a table called EVENT\$1EXAMPLE based on the EVENT table. This CREATE AS command uses the DATE\$1PART function to create a date column (called PGDATE\$1PART by default) to store the epoch value for each event.
1. Selects the column and data type of EVENT\$1EXAMPLE from PG\$1TABLE\$1DEF.
1. Selects EVENTNAME, STARTTIME, and PGDATE\$1PART from the EVENT\$1EXAMPLE table to view the different date and time formats.
1. Selects EVENTNAME and STARTTIME from EVENT EXAMPLE as is. Converts epoch values in PGDATE\$1PART using a 1 second interval to a timestamp without time zone, and returns the results in a column called CONVERTED\$1TIMESTAMP.
```
create table event_example
as select eventname, starttime, date_part(epoch, starttime) from event;
select "column", type from pg_table_def where tablename='event_example';
column | type
---------------+-----------------------------
eventname | character varying(200)
starttime | timestamp without time zone
pgdate_part | double precision
(3 rows)
```
```
select eventname, starttime, pgdate_part from event_example;
eventname | starttime | pgdate_part
----------------------+---------------------+-------------
Mamma Mia! | 2008-01-01 20:00:00 | 1199217600
Spring Awakening | 2008-01-01 15:00:00 | 1199199600
Nas | 2008-01-01 14:30:00 | 1199197800
Hannah Montana | 2008-01-01 19:30:00 | 1199215800
K.D. Lang | 2008-01-01 15:00:00 | 1199199600
Spamalot | 2008-01-02 20:00:00 | 1199304000
Macbeth | 2008-01-02 15:00:00 | 1199286000
The Cherry Orchard | 2008-01-02 14:30:00 | 1199284200
Macbeth | 2008-01-02 19:30:00 | 1199302200
Demi Lovato | 2008-01-02 19:30:00 | 1199302200
select eventname,
starttime,
timestamp with time zone 'epoch' + pgdate_part * interval '1 second' AS converted_timestamp
from event_example;
eventname | starttime | converted_timestamp
----------------------+---------------------+---------------------
Mamma Mia! | 2008-01-01 20:00:00 | 2008-01-01 20:00:00
Spring Awakening | 2008-01-01 15:00:00 | 2008-01-01 15:00:00
Nas | 2008-01-01 14:30:00 | 2008-01-01 14:30:00
Hannah Montana | 2008-01-01 19:30:00 | 2008-01-01 19:30:00
K.D. Lang | 2008-01-01 15:00:00 | 2008-01-01 15:00:00
Spamalot | 2008-01-02 20:00:00 | 2008-01-02 20:00:00
Macbeth | 2008-01-02 15:00:00 | 2008-01-02 15:00:00
The Cherry Orchard | 2008-01-02 14:30:00 | 2008-01-02 14:30:00
Macbeth | 2008-01-02 19:30:00 | 2008-01-02 19:30:00
Demi Lovato | 2008-01-02 19:30:00 | 2008-01-02 19:30:00
...
```
DECADE or DECADES
Amazon Redshift interprets the DECADE or DECADES DATEPART based on the common calendar. For example, because the common calendar starts from the year 1, the first decade (decade 1) is 0001-01-01 through 0009-12-31, and the second decade (decade 2) is 0010-01-01 through 0019-12-31. For example, decade 201 spans from 2000-01-01 to 2009-12-31:
```
select extract(decade from timestamp '1999-02-16 20:38:40');
date_part
-----------
200
select extract(decade from timestamp '2000-02-16 20:38:40');
date_part
-----------
201
select extract(decade from timestamp '2010-02-16 20:38:40');
date_part
-----------
202
```
MIL or MILS
Amazon Redshift interprets a MIL to start with the first day of year *\$1001* and end with the last day of year `#000`:
```
select extract (mil from timestamp '2000-12-16 12:21:13');
date_part
-----------
2
select extract (mil from timestamp '2001-12-16 12:21:13');
date_part
-----------
3
```