clock

Timestamps, Aargh!

Share this post on:

Math… the universal language. Timestamps, not so much.

The way we decide to denote date and time differs across both computer languages and human languages. The format also differs across implementations of SQL. For example, Oracle and Postgres allow very different formats to be entered in the timestamp data type.

Oracle allows a wide variety of punctuation in dates: hyphens, slashes, commas, periods, colons. Postgres supports a more limited list.

Here’s a test using all of the sample formats on the date/time Postgres documentation page:

create table my_table (my_time_field timestamptz not null);

insert into my_table (my_time_field) values ('1999-01-08');
insert into my_table (my_time_field) values ('January 8, 1999');
insert into my_table (my_time_field) values ('1/8/1999');
insert into my_table (my_time_field) values ('01/08/99');
insert into my_table (my_time_field) values ('1999-Jan-08');
insert into my_table (my_time_field) values ('Jan-08-1999');
insert into my_table (my_time_field) values ('08-Jan-1999');
insert into my_table (my_time_field) values ('99-Jan-08');
insert into my_table (my_time_field) values ('08-Jan-99');
insert into my_table (my_time_field) values ('Jan-08-99');
insert into my_table (my_time_field) values ('19990108');
insert into my_table (my_time_field) values ('990108');
insert into my_table (my_time_field) values ('1999.008');
insert into my_table (my_time_field) values ('January 8, 99 AD');
insert into my_table (my_time_field) values ('J2451187');

You may notice that some of these can be ambiguous depending on your country. For example, in the US, 1/8 is January 8th, but in Peru it would be August 1st. But if there’s one thing databases are not, it is ambiguous. This date format is controlled by a file and variable:

show DateStyle;

 DateStyle
-----------
 ISO, MDY

Tip: If you’re importing data from one system to another, best to check your DateStyle before you end up transposing a bunch of months and days.

The input format is pretty forgiving, and all of our inserts end up with the same timestamp:

select * from my_table;

     my_time_field
------------------------
 1999-01-08 00:00:00-05
 1999-01-08 00:00:00-05
 1999-01-08 00:00:00-05
 1999-01-08 00:00:00-05
 1999-01-08 00:00:00-05
 1999-01-08 00:00:00-05
 1999-01-08 00:00:00-05
 1999-01-08 00:00:00-05
 1999-01-08 00:00:00-05
 1999-01-08 00:00:00-05
 1999-01-08 00:00:00-05
 1999-01-08 00:00:00-05
 1999-01-08 00:00:00-05
 1999-01-08 00:00:00-05

What about the time format? After all, my_time_field is a timestamp field. Again, using the examples from the Postgres documentation:

insert into my_table (my_time_field) values ('1999-01-08 04:05:06.789');
insert into my_table (my_time_field) values ('1999-01-08 04:05:06');
insert into my_table (my_time_field) values ('1999-01-08 04:05');
insert into my_table (my_time_field) values ('1999-01-08 040506');
insert into my_table (my_time_field) values ('1999-01-08 04:05 AM');
insert into my_table (my_time_field) values ('1999-01-08 04:05:06.789-5');
insert into my_table (my_time_field) values ('1999-01-08 04:05:06-05:00');
insert into my_table (my_time_field) values ('1999-01-08 04:05-05:00');
insert into my_table (my_time_field) values ('1999-01-08 040506-05');
insert into my_table (my_time_field) values ('1999-01-08 04:05:06 EST');
insert into my_table (my_time_field) values ('1999-01-08 04:05:06 America/New_York');

And the times are about what I expect:

select * from my_table ;

       my_time_field
----------------------------
 1999-01-08 04:05:06-05
 1999-01-08 04:05:06-05
 1999-01-08 04:05:06-05
 1999-01-08 04:05:00-05
 1999-01-08 04:05:06-05
 1999-01-08 04:05:06.789-05
 1999-01-08 04:05:06-05
 1999-01-08 04:05:00-05
 1999-01-08 04:05:06-05
 1999-01-08 04:05:00-05
 1999-01-08 04:05:06.789-05

Note this nugget from the documentation: “When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone, and displayed as local time in that zone.

show TimeZone;

  TimeZone
------------
 US/Eastern

Digging further in the documentation to see the algorithm for date and time interpretation, Postgres supports dashes, slashes, and periods in dates. We tried dashes and slashes above, but let’s revisit the periods.

insert into my_table (my_time_field) values ('1999-01-08 04:05:06');
insert into my_table (my_time_field) values ('1999.01.08 04:05:06');

select * from my_table ;

     my_time_field
------------------------
 1999-01-08 04:05:06-05
 1999-01-08 04:05:06-05

Looks good. But note that Postgres doesn’t interpret periods in the time portion like some other databases can:

insert into my_table (my_time_field) values ('1999-01-08 04.05.06');

ERROR:  invalid input syntax for type timestamp with time zone: "1999-01-08 04.05.06"
LINE 1: insert into my_table (my_time_field) values ('1999-01-08 04....

So who uses periods (or dots or… and here’s a hint… full stops) in dates? Check out the style guide from The Guardian:

times
1am, 6.30pm, etc; 10 o’clock last night but 10pm yesterday; half past two, a quarter to three, 10 to 11, etc; 2hr 5min 6sec, etc; for 24-hour clock, 00.47, 23.59; noon, midnight (not 12 noon, 12 midnight or 12am, 12pm).

https://www.theguardian.com/guardian-observer-style-guide-t

Then how do we get a bunch of timestamps into Postgres if the times are separated with periods? I thought you’d never ask.

We can transform it using the to_timestamp function in Postgres, providing the input format as the second argument to the function:

insert into my_table (my_time_field) values (to_timestamp('1999-01-08 04.05.06', 'YY-MM-dd HH.MI.ss'));

Works like a charm:

select * from my_table ;

     my_time_field
------------------------
 1999-01-08 04:05:06-05

If for any reason you have dates or times formatted in other ways, check out this table with various formatting codes.

Drop me an email or comment below with your latest timestamp nightmare story. I’d love to hear it!

Author: Valerie Parham-Thompson

View all posts by Valerie Parham-Thompson >