PostgreSQL Reference Manual - Volume 1 - SQL Language Reference by The PostgreSQL Global Development Group Paperback (6"x9"), 716 pages ISBN 0954612027 RRP £32.00 ($49.95) Sales of this book support the PostgreSQL project! Get a printed copy>>>

PostgreSQL Reference Manual Volume 1 Buy the book here! >>> support free documentation

7.9.2 date_trunc

The function date_trunc is conceptually similar to the trunc function for numbers.

date_trunc('field', source)

source is a value expression of type timestamp or interval. (Values of type date and time are cast automatically, to timestamp or interval respectively.) field selects to which precision to truncate the input value. The return value is of type timestamp or interval with all fields that are less significant than the selected one set to zero (or one, for day and month).

Valid values for field are:

• microseconds
• milliseconds
• second
• minute
• hour
• day
• week
• month
• quarter
• year
• decade
• century
• millennium

Examples:

SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-02-16 20:00:00

SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-01-01 00:00:00