Epoch time is both a blessing and a curse. It is super-convenient for counting seconds (and doing calculations based on them) but can also be a pain to try to get into something readable as, or comparable to, a recognizable date. So today we’ll get into and out of epoch to show its flexibility without our brains having to be contortionists too.
If you’ve worked with datetime formats at all in PostgreSQL (or any other SQL version) before, you’ve probably dealt with date_part (or extract). In pgSQL, this will pull out the numeric value of the part of the date that you have specified. For instance:
/* Pull the Month from the timestamp without time zone */ date_part('month','2012-03-13 12:45:22') /*Yields*/ 3
Another common one if you’re trying to eliminate the time portion is to truncate the date using date_trunc.
/* Truncate date so all timestamps map to midnight */ date_trunc('day','2012-03-13 12:45:22') /* Yields */ '2012-03-13 00:00:00'
Earlier today, I went looking for a similar construct to extract and isolate only the time portion and ran across a rather cool little feature. Seems there is a built-in function housed in the pg_catalog schema that allows for very quick parsing. It’s called, unsurprisingly, time.
Extracting Time Part from Timestamp Example
select pg_catalog.time('2012-03-13 12:45:22'); /* Yields */ '12:45:22'
It may not look like much code (which, to be fair, I greatly appreciated), but this works like a charm. You can then use the time syntax for any grouping or editing you want to do. For instance, this was being used to chunk up the day into meal periods for a client analysis so we could state that transaction_time between ’12:00:00′ and ’15:00:00′ is lunch with a simple case statement.
After finding this awesome little tidbit, I did some more research on this mysterious pg_catalog schema and it turns out that there are TONS of functions hidden away in there (including one simply called ‘date’ that may suit better than date_trunc if you want only the date portion). Found a pretty comprehensive schema of pg_catalog here and will be testing out the functions as needed.
Do you have a better way of handling different components of dates and timestamps? I’d love to hear about it. Please leave a comment below.