DEV Community

mixbo
mixbo

Posted on

Right way store datetime to database.

Alt Text

In javascript date api

new Date() // Mon Jun 01 2020 20:25:36 GMT+0800 (中国标准时间)
Enter fullscreen mode Exit fullscreen mode

you can see string with timezone with +0800 but should we store into database with timezone? i don't think so.

Database has timezone settings also os system has timezone settings too. if we database set timezone with other value after read records from db we maybe got wrong value with different timezone.

The best way store datetime to database can store absolut time value to database. absolut time if number with from 1970-01-01 00:00:00 to
now and get seconds.

PS: Java and Javascript will return milliseconds different other language

Stroe absolut time in database benefit:

  • we can render time string with different timezone and right value.
  • we can caculation two datetime range value, got two time pass away

Also have bad side:

  • we can't direct know database field datetime just can see 1591015260 not readable.

Latest comments (3)

Collapse
 
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 :-)

Collapse
 
trasherdk profile image
TrasherDK

People, a lot smarter than me, have had long time to figure stuff like this out.

You might want to take a look at The DATE, DATETIME, and TIMESTAMP Types

Collapse
 
ihavecoke profile image
mixbo

you're right 👍