Data Types - PostgreSQL: Up and Running - page 89

period for which start and end are identical). Watch out for this if you're a avid user of
the OVERLAPS function.
Example 5-10. OVERLAPS for timestamp and date
SELECT ( '2012-10-25 10:00 AM' :: timestamp , '2012-10-25 2:00 PM' :: timestamp ) OVERLAPS
( '2012-10-25 11:00 AM' :: timestamp , '2012-10-26 2:00 PM' :: timestamp ) AS x ,
( '2012-10-25' :: date , '2012-10-26' :: date ) OVERLAPS
( '2012-10-26' :: date , '2012-10-27' :: date ) As y ;
x |y
---+---
t |f
In addition to the operators, PostgreSQL comes with functions supporting temporal
types. A full listing can be found at Datetime Functions and Operators . We'll demon‐
strate a sampling here.
Once again, we start with the versatile generate_series function. You can use this
function with temporal types and interval steps.
As you can see in Example 5-11 , we can express dates in our local datetime format or
the more global ISO Y-M-D format. PostgreSQL automatically interprets differing input
formats. To be safe, we tend to stick with entering dates in ISO, because date formats
vary from culture to culture, server to server, or even database to database.
Example 5-11. Generate time series using generate_series()
SELECT ( dt - interval '1 day' ):: date As eom
FROM generate_series ( '2/1/2012' , '6/30/2012' , interval '1 month' ) As dt ;
eom
------------
2012-01-31
2012-02-29
2012-03-31
2012-04-30
2012-05-31
Another popular activity is to extract or format parts of a datetime value. Here, the
functions date_part and to_char fit the bill. Example 5-12 also drives home the be‐
havior of DST for a time-zone-aware data type. We intentionally chose a period that
crosses a daylight saving switchover in US/East . Because the clock springs forward at 2
a.m., the final row of the table reflects the new time.
Example 5-12. Extracting elements of a datetime
SELECT dt , date_part ( 'hour' , dt ) As mh , to_char ( dt , 'HH12:MI AM' ) As tm
FROM
generate_series ( '2012-03-11 12:30 AM' , '2012-03-11 3:00 AM' , interval '15 minutes'
) As dt ;