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

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.  Add to this the fairly primitive nature of SQL and you have a whole barrel of monkey fun.

If you run psql from your command prompt and type

SELECT now();

it will return something like:

1970-01-01 18:05:48.820014

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.  It is certainly too high if you are going to be comparing it with a <ctime> struct tm whose highest resolution is seconds.

If we compare a timestamp like the one above to one derived from our <ctime> struct tm it will never succeed so we have to bring the resolution down a little.  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 function.

SELECT DATE_TRUNC('minutes', now());

will return something like

2017-09-21 20:09:00+01

The resolutions are for DATE_TRUNC are:

• microseconds
• milliseconds
• hour
• day
• week
• month
• quarter
• year
• decade
• century
• millennium

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:

SELECT DATE_TRUNC('minutes', now() AT TIME ZONE 'Europe/London');

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 using the strptime(); function)

2017-09-21 21:06:00

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:

SELECT * FROM pg_timezone_names;

in your psql shell.

Time stamps are incredibly versatile and useful but can also present all sorts of issues if you don’t properly understand them.  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.  This post demonstrates some basic stuff but is far from a definitive guide.