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.
