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

DATE_PART Function

DATE_PART extracts datepart values from an expression. DATE_PART is a synonym of the PGDATE_PART function.


DATE_PART ( datepart, {date|timestamp} )



The specific part of the date value (year, month, or day, for example) that the function operates on. For more information, see Dateparts for Date or Time Stamp Functions.


A date or timestamp column or an expression that implicitly converts to a date or time stamp. The expression must be a date or time stamp expression that contains the specified datepart.

Return Type



Apply the DATE_PART 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 dateparts in full or abbreviate them; in this case, w stands for weeks.

The day of week datepart returns an integer from 0-6, starting with Sunday. Use DATE_PART 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)

Apply the DATE_PART function to a literal date value:

select date_part(minute, '2009-01-01 02:08:01'); pgdate_part ------------- 8 (1 row)

The default column name for the DATE_PART function is PGDATE_PART.