In javascript date api
new Date() // Mon Jun 01 2020 20:25:36 GMT+0800 (中国标准时间)
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:
JavaandJavascriptwill 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
1591015260not readable.
Latest comments (3)
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 :-)
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
you're right 👍