TIMESTAMPS在DATETIME字段上有多普遍? [英] How common are TIMESTAMPS over DATETIME fields?

问题描述

我希望我的产品像其他所有Web产品一样，能够从数据库中提取数据信息并将其转换为用户的时区，然后再显示在页面上.我正在使用PHP和MySQL.

I want my product, like every other web product out there, to pull a data information from my database and convert it to the user's timezone before displaying it on a page. I am using PHP and MySQL.

使用TIMESTAMP字段是否比使用DATETIME字段更普遍?

Is the use of TIMESTAMP fields more common than the use of DATETIME fields?

根据我的研究，似乎我必须使用DATETIME字段

From my research, it seems that using DATETIME fields, I would have to

1. 将所有日期信息存储在UTC中
每次启动应用程序时
2. 调用date_default_timezone_set('UTC')
3. 从使用基于当前日期/时间的date()创建的日期中手动减去用户的UTC偏移时间
4. 从传递给date()并由date()设置格式的现有日期中手动减去用户的UTC偏移时间

1. Store all date information in UTC
2. call date_default_timezone_set('UTC') each time the application starts
3. manually subtract the user's UTC offset hours from dates created using date() being based on the current date/time
4. manually subtract the user's UTC offset hours from existing dates passed to and formatted by date()

在我的日期字段中使用TIMESTAMPS，似乎仅在建立数据库连接时才需要运行以下命令:SET time_zone ='用户时区'(例如America/Los_Angelos).

Using TIMESTAMPS for my date fields, it seems I will only have to run the following when a database connection is made: SET time_zone='the users timezone' (eg. America/Los_Angelos).

考虑到这一点，我可以推断出大多数人倾向于使用TIMESTAMP而不是DATETIME ...这是准确的吗?

Considering this, I would deduce most people would be inclined to using TIMESTAMPs rather than DATETIMEs...is this accurate?

推荐答案

来自

SQL Server时间戳数据类型具有 与时间或日期无关.的SQL 服务器时间戳是二进制数 表示中的相对顺序 发生了哪些数据修改 数据库.时间戳数据类型 最初是为了支持 SQL Server恢复算法.

The SQL Server timestamp data type has nothing to do with times or dates. SQL Server timestamps are binary numbers that indicate the relative sequence in which data modifications took place in a database. The timestamp data type was originally implemented to support the SQL Server recovery algorithms.

为回答您的问题，DATETIME被更常用，因为TIMESTAMP不能用于存储一般的日期/时间信息.

to answer your question, DATETIME is more commonly used, as TIMESTAMP is not intended to be used for storing general Date / Time information.

这篇关于TIMESTAMPS在DATETIME字段上有多普遍?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！