4 Tips for Working with Dates in PostgreSQLPublished on Aug 26, 2013

Those of us who come from Rails aren’t surprised when we see something like 5.weeks.from_now or 3.days.ago + 2.hours, which makes working with dates much easier. But PostgreSQL got your back on this as well, you can just use the builtin functions and get most of the same functionality.

Current Time/Date/Timestamp

There are many ways of getting a current time, but first we need to distinguish between two types

1. always returns current value (clock_timestamp())
2. always returns current value, unless in a transaction, in which case it returns the value from the beginning of the transaction (now())

Let’s take a look at an example

postgres=# BEGIN;
postgres=# SELECT now();
              now
-------------------------------
 2013-08-26 12:17:43.182331+02

postgres=# SELECT now();
              now
-------------------------------
 2013-08-26 12:17:43.182331+02

postgres=# SELECT clock_timestamp();
        clock_timestamp
-------------------------------
 2013-08-26 12:17:50.698413+02

postgres=# SELECT clock_timestamp();
        clock_timestamp
-------------------------------
 2013-08-26 12:17:51.123905+02

As you can see, clock_timestamp() changes every time the statement is executed, but now() always returns the same value. It’s also worth noting that both of these functions take timezone into account.

Time interval, aka 3.days.ago

You can easily create time intervals using the interval operator, for example

• interval '1 day'
• interval '5 days'
• interval '5 days' + interval '3 hours'
• interval '5 days 3 hours'

As you can see, we can do simple math using the interval operator, which makes it very easy to construct things like 3.days.ago just by doing the following

postgres=# SELECT now() - interval '3 days';
           ?column?
-------------------------------
 2013-08-23 12:23:40.069717+02

Extracting the day of the week and more

Sometimes you just want to know the day of the week for a given date, or the century, or just the day. PostgreSQL has an extract() function which does just this.

Just to put this into context the examples were executed on Monday, August 26.

postgres=# SELECT extract(DAY FROM now());
 date_part
-----------
        26

postgres=# SELECT extract(DOW FROM now());
 date_part
-----------
         1

You can do much more with extract(), for a complete list of examples take a look at the official documentation. Here’s just a few

• day
• century
• dow (day of week)
• doy (day of year)
• minute
• month
• year

Converting between timezones

Sometimes it is useful to show a specific date in a different timezone, which is exactly what the AT TIME ZONE construct is for. Let’s take a look at how it works. We’ll do this in a transaction so that the now() function always returns a same value and we can easily see the difference in hours.

postgres=# BEGIN;
BEGIN
postgres=# SELECT now();
              now
-------------------------------
 2013-08-26 12:39:39.122218+02

postgres=# SELECT now() AT TIME ZONE 'GMT';
          timezone
----------------------------
 2013-08-26 10:39:39.122218

postgres=# SELECT now() AT TIME ZONE 'GMT+1';
          timezone
----------------------------
 2013-08-26 09:39:39.122218

postgres=# SELECT now() AT TIME ZONE 'PST';
          timezone
----------------------------
 2013-08-26 02:39:39.122218

Do you manage email campaigns for your business?

We're building a tool to help businesses reach out to their customers more easily. It's called SendingBee and it's going to be awesome.

I Want To Know More