一次对于时间存储方式的讨论

上周的某一天,大家突然开始说起了公司的时间存储格式,有个同学的公司的时间返回值特别奇怪,迷之诡异,无法在前端填入Date()解析,这种时候我就想到了——时间戳大法好,下一个。

——嗯,真的是这样吗?

然后我们掏出 MySQL 的文档:

The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.

The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

A DATETIME or TIMESTAMP value can include a trailing fractional seconds part in up to microseconds (6 digits) precision. In particular, any fractional part in a value inserted into a DATETIME or TIMESTAMP column is stored rather than discarded. With the fractional part included, the format for these values is 'YYYY-MM-DD HH:MM:SS[.fraction]', the range for DATETIME values is '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999', and the range for TIMESTAMP values is '1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.999999'. The fractional part should always be separated from the rest of the time by a decimal point; no other fractional seconds delimiter is recognized.

好啦,取值范围不一样,2038 问题似乎确实是一个比较严肃的问题,那么似乎还是取到 9999 年的比较靠谱,换言之,当有 DATE 的时候,数据库里存储 DATE 就行了,然而返回的时候,如果返回时间戳,那么就是前功尽弃,所以需要一个时间的标准格式:https://www.ietf.org/rfc/rfc3339.txt

通过这一格式由后端返回给前端,前端可以通过new Date(dateTime)直接解析。

这里其实要说的就是,返回的时间必须是时区相关的,看到一些存储时返回的日期值是只有日期而没有时区的,非常不靠谱,正巧讨论之前几天就在 StackOverflow 回答过一个时间处理的问题,连起来想想还是挺有意思的。

标签: 知识

添加新评论