{"id":33,"date":"2017-09-21T21:34:13","date_gmt":"2017-09-21T20:34:13","guid":{"rendered":"https:\/\/blog.inplico.uk\/?p=33"},"modified":"2017-09-21T21:36:11","modified_gmt":"2017-09-21T20:36:11","slug":"sql-time-stamps","status":"publish","type":"post","link":"https:\/\/blog.inplico.uk\/?p=33","title":{"rendered":"SQL Time stamps"},"content":{"rendered":"

Throughout my program I use time stamps all over the place for all sorts of different purposes in all sorts of different formats.\u00a0 This post is to give you an overview of the basics.\u00a0 There is no C++ or Ncurses code here, any of the code is commands that can be typed at a psql<\/strong> command prompt.<\/p>\n

For us humans adding and multiplying times is fairly easy, we just work out how many hours or how many days etc, but for computers it is not so easy.\u00a0 Add to this the fairly primitive nature of SQL and you have a whole barrel of monkey fun.<\/p>\n

If you run psql from your command prompt and type<\/p>\n

SELECT now();<\/pre>\n
it will return something like:<\/p>\n
1970-01-01 18:05:48.820014<\/pre>\n
Now this might be exactly what you want if you are trying to find when a record was last updated for example, but if you are using the timestamp for start and finish times on a job for example then this resolution is probably a little high.\u00a0 It is certainly too high if you are going to be comparing it with a <ctime> struct tm<\/a><\/strong> whose highest resolution is seconds<\/strong>.<\/p>\n
If we compare a timestamp like the one above to one derived from our <ctime> struct tm<\/strong>\u00a0it will never succeed so we have to bring the resolution down a little.\u00a0 Let us say for example that we only want the resolution down to the nearest minute, all we need to do is use the DATE_TRUNC<\/a><\/strong> function.<\/p>\n
SELECT DATE_TRUNC('minutes', now());<\/pre>\n
will return something like<\/p>\n
2017-09-21 20:09:00+01<\/pre>\n
The resolutions are for DATE_TRUNC are:<\/p>\n
    \n
  • microseconds<\/li>\n
  • milliseconds<\/li>\n
  • hour<\/li>\n
  • day<\/li>\n
  • week<\/li>\n
  • month<\/li>\n
  • quarter<\/li>\n
  • year<\/li>\n
  • decade<\/li>\n
  • century<\/li>\n
  • millennium<\/li>\n<\/ul>\n
    The +01 appending the timestamp denotes the current timezone which in my case is GMT +1 if we want to get rid of this then we need to tell postgres what time zone we are using. to do so we simply do as follows:<\/p>\n
    SELECT DATE_TRUNC('minutes', now() AT TIME ZONE 'Europe\/London');<\/pre>\n
    Which will return the time stamp without the +XX (which is more than likely what you want if you are copying it to a <ctime> tm struct<\/strong> using the strptime();<\/strong> function)<\/p>\n
    2017-09-21 21:06:00<\/pre>\n
    Obviously if you are not in the GMT timezone then this is of little use to you so if you want to get a list of available time zones then you need to type:<\/p>\n
    SELECT * FROM pg_timezone_names;<\/pre>\n
    in your psql shell.<\/p>\n
    Time stamps are incredibly versatile and useful but can also present all sorts of issues if you don’t properly understand them.\u00a0 For this reason many programmers go out of their way to avoid them altogether unless absolutely necessary, but once you understand them they are indispensable.\u00a0 This post demonstrates some basic stuff but is far from a definitive guide.<\/p>\n","protected":false},"excerpt":{"rendered":"
    Throughout my program I use time stamps all over the place for all sorts of different purposes in all sorts of different formats.\u00a0 This post is to give you an overview of the basics.\u00a0 There is no C++ or Ncurses code here, any of the code is commands that can be typed at a psql […]<\/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-33","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\/33","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=33"}],"version-history":[{"count":5,"href":"https:\/\/blog.inplico.uk\/index.php?rest_route=\/wp\/v2\/posts\/33\/revisions"}],"predecessor-version":[{"id":38,"href":"https:\/\/blog.inplico.uk\/index.php?rest_route=\/wp\/v2\/posts\/33\/revisions\/38"}],"wp:attachment":[{"href":"https:\/\/blog.inplico.uk\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=33"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.inplico.uk\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=33"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.inplico.uk\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=33"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}