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

Dateparts for Date or Time Stamp Functions

The following table identifies the datepart and timepart names and abbreviations that are accepted as arguments to the following functions:

  • DATEADD

  • DATEDIFF

  • DATE_PART

  • DATE_TRUNC

  • EXTRACT

Datepart or Timepart Abbreviations
millennium, millennia mil, mils
century, centuries c, cent, cents
decade, decades dec, decs
epoch epoch (supported by the DATE_PART and the EXTRACT)
year, years y, yr, yrs
quarter, quarters qtr, qtrs
month, months mon, mons
week, weeks

w

When used with the DATE_TRUNC, returns the date for the most recent Monday.

day of week

dayofweek, dow, dw, weekday (supported by the DATE_PART and the EXTRACT Function)

Returns an integer from 0–6, starting with Sunday.

Note

The DOW datepart behaves differently from the day of week (D) date part used for datetime format strings. D is based on integers 1–7, where Sunday is 1. For more information, see Datetime Format Strings.

day of year dayofyear, doy, dy, yearday (supported by the DATE_PART and the EXTRACT)
day, days d
hour, hours h, hr, hrs
minute, minutes m, min, mins
second, seconds s, sec, secs
millisecond, milliseconds ms, msec, msecs, msecond, mseconds, millisec, millisecs, millisecon
microsecond, microseconds microsec, microsecs, microsecond, usecond, useconds, us, usec, usecs
timezone, timezone_hour, timezone_minute Supported by the DATE_TRUNC function and the EXTRACT for time stamp with time zone (TIMESTAMPTZ) only.

Variations in Results with Seconds, Milliseconds, and Microseconds

Minor differences in query results occur when different date functions specify seconds, milliseconds, or microseconds as dateparts:

  • The EXTRACT function return integers for the specified datepart only, ignoring higher- and lower-level dateparts. If the specified datepart is seconds, milliseconds and microseconds are not included in the result. If the specified datepart is milliseconds, seconds and microseconds are not included. If the specified datepart is microseconds, seconds and milliseconds are not included.

  • The DATE_PART function returns the complete seconds portion of the time stamp, regardless of the specified datepart, returning either a decimal value or an integer as required.

For example, compare the results of the following queries:

Copy
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 (1 row) select date_part(sec, micro) from seconds; pgdate_part ------------- 3.189717 (1 row)

CENTURY, EPOCH, DECADE, and MIL Notes

CENTURY or CENTURIES

Amazon Redshift interprets a CENTURY to start with year ###1 and end with year ###0:

Copy
select extract (century from timestamp '2000-12-16 12:21:13'); date_part ----------- 20 (1 row) select extract (century from timestamp '2001-12-16 12:21:13'); date_part ----------- 21 (1 row)
EPOCH

The Amazon Redshift implementation of EPOCH is relative to 1970-01-01 00:00:00.000000 independent of the time zone where the server resides. You might need to offset the results by the difference in hours depending on the time zone where the server is located.

The following example demonstrates the following:

  1. Creates a table called EVENT_EXAMPLE based on the EVENT table. This CREATE AS command uses the DATE_PART function to create a date column (called PGDATE_PART by default) to store the epoch value for each event.

  2. Selects the column and data type of EVENT_EXAMPLE from PG_TABLE_DEF.

  3. Selects EVENTNAME, STARTTIME, and PGDATE_PART from the EVENT_EXAMPLE table to view the different date and time formats.

  4. Selects EVENTNAME and STARTTIME from EVENT EXAMPLE as is. Converts epoch values in PGDATE_PART using a 1 second interval to a timestamp without time zone, and returns the results in a column called CONVERTED_TIMESTAMP.

Copy
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)
Copy
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:

Copy
select extract(decade from timestamp '1999-02-16 20:38:40'); date_part ----------- 200 (1 row) select extract(decade from timestamp '2000-02-16 20:38:40'); date_part ----------- 201 (1 row) select extract(decade from timestamp '2010-02-16 20:38:40'); date_part ----------- 202 (1 row)
MIL or MILS

Amazon Redshift interprets a MIL to start with the first day of year #001 and end with the last day of year #000:

Copy
select extract (mil from timestamp '2000-12-16 12:21:13'); date_part ----------- 2 (1 row) select extract (mil from timestamp '2001-12-16 12:21:13'); date_part ----------- 3 (1 row)