Working with DATE
, TIMESTAMP
, and INTERVAL
in PostgreSQL can be confusing. In this article I will go over the three date/time related data types, and the two most useful date/time functions: DATE_PART
and DATE_TRUNC
. Finally, I will provide some real life examples of how these types and functions can be used within queries.
PostgreSQL Date/Time Documentation
The DATE
type contains the year
, month
, and day
of a date. It is not possible to do any type of time
related functions on a DATE
without first converting it to a TIMESTAMP
. Subtracting two DATE
values from one another results in an INT
representing the # of days between.
The TIMESTAMP
type contains a year
, month
, day
, hour
, minute
, second
, and microsecond
. This is the type that I most often use.