DEV Community 👩‍💻👨‍💻

DEV Community 👩‍💻👨‍💻 is a community of 963,503 amazing developers

We're a place where coders share, stay up-to-date and grow their careers.

Create account Log in
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.

Oldest comments (3)

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 Author

you're right 👍

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

Need a better mental model for async/await?

Check out this classic DEV post on the subject.

⭐️🎀 JavaScript Visualized: Promises & Async/Await

async await