DEV Community

Discussion on: Right way store datetime to database.

jmcp profile image
James McPherson

If I really, really have to store a timestamp in a db column without using the db's native timestamp datatype, I'm going to use seconds-since-epoch - and I'm also going to store the timezone offset from UTC.

Fortunately for us mortals, though, the major databases all have a variant of TIMESTAMP WITH TIME ZONE (that's Oracle syntax) which does all the work for us.

There was one counter-example I had recently, though - the pipeline I was writing needed to store a last-modified timestamp from the input in the column. Rather than parse the input's text (eg 2020-02-03T13:00Z) into the native format, I simply stored it as a string. Why? Purely because all I needed to do was check whether that text was the same on the next run of the pipeline. Laziness worked out this time :-)