When programming, sometimes it’s useful to extract various time components from a Date-time field in SQL queries.  For example, you might want to filter the date field by, hour, or year (or both).  Fortunately, Postgres has a easy way to pull this data out within the query itself:

SELECT EXTRACT(hour FROM a_datetime_field) FROM a_table;

If the field was the value ‘4/5/2009 13:09:00’ then that above query would return “13” in the select.

In a stranger, practical example, I was moving a database from one server to another and for some unknown reason, all the dates in a table were fudged so that instead of being the year 2009, it was 142009, and the seconds were also stuffed up – the result being that any queries I ran against the table threw ADO.NET exceptions because the dates couldn’t be parsed properly.  I needed to run a query like this one to set things right again:

update atable
  set datetimeadded = cast( '2009-' || EXTRACT(month FROM datetimeadded) || '-' ||
    EXTRACT(day FROM datetimeadded) || ' ' || EXTRACT(hour FROM datetimeadded) || ':' ||
    EXTRACT(minute FROM datetimeadded) as timestamp),
  datetimeupdated =  cast('2009-' || EXTRACT(month FROM datetimeupdated) || '-' ||
    EXTRACT(day FROM datetimeupdated) || ' ' || EXTRACT(hour FROM datetimeupdated) || ':' ||
    EXTRACT(minute FROM datetimeupdated) as timestamp);

The casts are needed to get the update to cooperate, but basically this recreates the date time piece-by-piece based on the original date-time value.