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.