{"id":39,"date":"2017-09-21T23:24:48","date_gmt":"2017-09-21T22:24:48","guid":{"rendered":"https:\/\/blog.inplico.uk\/?p=39"},"modified":"2017-09-21T23:30:23","modified_gmt":"2017-09-21T22:30:23","slug":"postgresql-time-epoch","status":"publish","type":"post","link":"https:\/\/blog.inplico.uk\/?p=39","title":{"rendered":"Postgresql Time (epoch)"},"content":{"rendered":"

In addition to standard timestamps<\/a> we can also ask postgres to return the number of seconds from the epoch.\u00a0 To the uninitiated the epoch is usually the number of seconds past a fixed point in time (usually, but not always, midnight on 01\/01\/1970).\u00a0 If you would like to know when the epoch is on your system then from a psql<\/strong> shell enter the following command:<\/p>\n

postgres=# SELECT TO_TIMESTAMP(0);<\/pre>\n
my system returned 01\/01\/1970 as expected:<\/p>\n
      to_timestamp\r\n------------------------\r\n 1970-01-01 00:00:00+00\r\n(1 row)<\/pre>\n
If we want to return a timestamp using seconds from the epoch then we use the EXTRACT<\/strong> function<\/p>\n
postgres=# SELECT EXTRACT(EPOCH FROM now());<\/pre>\n
will return something like<\/p>\n
    date_part\r\n------------------\r\n 1506028038.21499\r\n(1 row)<\/pre>\n
As with standard timestamps this is a very high resolution however thankfully we can use the same functionality to tame our return value and get the resolution that we require.<\/p>\n
DATE_TRUNC<\/strong> and AT TIME ZONE<\/strong> work the same for epoch arguments as the do for standard timestamp examples so I am not going to go into a great deal of detail about them here, but here are a couple of examples to help you with the syntax:<\/p>\n
postgres=# SELECT EXTRACT(EPOCH FROM DATE_TRUNC('minutes' , now()));<\/pre>\n
will return something like<\/p>\n
 date_part\r\n------------\r\n 1506028320\r\n(1 row)<\/pre>\n
Unlike a standard timestamp, when returning seconds past the epoc rather than appending the string with +XX to denote the time zone, the function will add 3600 seconds for every hour so if you are in a timezone that is GMT +6 it will add 21600 to the value when you use AT TIME ZONE<\/strong><\/p>\n
postgres=# SELECT EXTRACT(EPOCH FROM DATE_TRUNC('minutes', now() AT TIME ZONE 'Europe\/London'));<\/pre>\n
if the value of now()<\/strong> is 1506028320<\/strong> without a time zone then now() AT TIME ZONE ‘Europe\/London’<\/strong> will return i.e. 1506028320 + 3600<\/strong><\/p>\n
 date_part \r\n------------ \r\n1506031920\r\n (1 row)<\/pre>\n
One of the big advantages of using seconds from the epoch is when performing time calculations.<\/p>\n","protected":false},"excerpt":{"rendered":"
In addition to standard timestamps we can also ask postgres to return the number of seconds from the epoch.\u00a0 To the uninitiated the epoch is usually the number of seconds past a fixed point in time (usually, but not always, midnight on 01\/01\/1970).\u00a0 If you would like to know when the epoch is on your […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,4],"tags":[],"class_list":["post-39","post","type-post","status-publish","format-standard","hentry","category-dates-times","category-a-series-of-hints-and-tips-when-using-the-postgresql-library-libpq-fe-h"],"_links":{"self":[{"href":"https:\/\/blog.inplico.uk\/index.php?rest_route=\/wp\/v2\/posts\/39","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.inplico.uk\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.inplico.uk\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.inplico.uk\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.inplico.uk\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=39"}],"version-history":[{"count":11,"href":"https:\/\/blog.inplico.uk\/index.php?rest_route=\/wp\/v2\/posts\/39\/revisions"}],"predecessor-version":[{"id":52,"href":"https:\/\/blog.inplico.uk\/index.php?rest_route=\/wp\/v2\/posts\/39\/revisions\/52"}],"wp:attachment":[{"href":"https:\/\/blog.inplico.uk\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=39"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.inplico.uk\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=39"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.inplico.uk\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=39"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}