如何最好在MySql中存储日期/时间？ [英] How best to store date/time in MySql?

问题描述

我应该存储Delphi TDateTIme，还是先转换为Unix时间戳？或者mayeb eeven作为一个字符串？

如何在MySql中声明列？作为Double或DateTime（如果我使用Unix时间戳，则为整数）？

Whta是正确的，或者如果我想要显示一个字符串与yyyy mm dd hh：mm：ss（或类似的），还可以从比较两个值得到经过的时间？

Btw，程序只会在一个tiemzone中使用 - 没有夏令时。

我很困惑，似乎无法在任何地方找到这个讨论。任何有用的URL？

解决方案

1. 
   

   一个Delphi TDateTIme，或者可能首先转换为Unix时间戳？

   
   

   通常情况下，您不应该这样做：数据库层中的数据类型应该自己有意义（尽可能），而不是依赖于你的应用来解释它们。正如 @Jim DeLaHunt所说的，这使数据库能够根据需要轻松地从SQL操作/解释它们（并且还可以让您轻松访问来自另一个应用程序代码库的相同数据在未来）。

   
   
   

   MySQL具有五个时间类型，其中只有两个存储日期和时间： DATETIME 和 TIMESTAMP 。

   
   
   

   正如其他人所暗示的，差异归结于您是否希望存储时区 - 虽然我发现这是一个令人困惑的方式：

   
   
   
   
   

   • TIMESTAMP 使用会话的 time_zone 变量将输入转换为UTC时间戳，然后重新输出输出：它对于指定exac很有用t时间;

   
   

   • DATETIME 只是存储日期和时间，而不考虑时区，很像拍摄日历和时钟的照片：用于指定在全球同一本地时间发生的事件。

   
   



2. 
   

   如何在MySql中声明列？作为Double或DateTime（如果我使用Unix时间戳，则为整数）？

   
   

   就像您声明任何其他列一样，您指定

   
   
   

   请注意， TIMESTAMP 具有其他功能，例如自动更新，如果需要，您可能希望在列声明中禁用它。 / p>




3. 
   

   Whta是正确的，或者如果我想要显示一个字符串，最简单的是yyyy mm dd hh ：mm：ss（或类似的），也可以从比较两个值得到经过的时间？

   
   

   上面的时间类型，你将能够做到这一切（使用日期功能）。 TIMESTAMP 和 DATETIME 类型的默认输出是'YYYY-MM-DD HH：MM：SS'格式。

   
   
   

   具体来说，比较两个值的经过时间可以通过MySQL的< a href =http://dev.mysql.com/doc/en/date-and-time-functions.html#function_timediff =nofollow noreferrer> TIMEDIFF（） 函数：

     SELECT TIMEDIFF（end，start） b $ b FROM my_table 
    WHERE ... 
     

Should I be storing a Delphi TDateTIme, or perhaps convert to Unix timestamp first? Or mayeb eeven as a string?

How should I declare the column in MySql? As a Double, or DateTime (or Integer if I use Unix timestamp)?

Whta is "correct", or what is easiest if I want to be able to display a string with "yyyy mm dd hh:mm:ss" (or similar) and also to be able to get an elapsed time from comparing two values?

Btw, the program will only ever be used in one tiemzone - which does not have daylight savings time.

I am confused and can't seem to find this discussed anywhere. Any helpful URLs?

解决方案

1. Should I be storing a Delphi TDateTIme, or perhaps convert to Unix timestamp first?

   Typically, you should do neither: the data types in the database layer ought to be meaningful (as possible) on their own and not depend on your application to interpret them. As @Jim DeLaHunt says, this enables the database to easily manipulate/interpret them from SQL as required (and also enables you to easily access the same data from another application codebase in the future).

   MySQL has five temporal types, only two of which store both a date and a time: DATETIME and TIMESTAMP.

   As others have alluded, the difference comes down to whether you wish to store the timezone - although I find that quite a confusing way of looking at it:

   • TIMESTAMP uses the session's time_zone variable to convert input into a UTC timestamp and then back again for output: it's useful for specifying an exact moment in time;

   • DATETIME simply stores the date and time without regard to timezone, much like taking a photograph of a calendar and clock: it's useful for specifying an event that occurs in the same local time globally.

2. How should I declare the column in MySql? As a Double, or DateTime (or Integer if I use Unix timestamp)?

   Just as you would declare any other column, you specify the relevant data type after the column name.

   Beware that TIMESTAMP has additional features, such as automatic update, which you may wish to disable in your column declaration if so desired.

3. Whta is "correct", or what is easiest if I want to be able to display a string with "yyyy mm dd hh:mm:ss" (or similar) and also to be able to get an elapsed time from comparing two values?

   Using one of the above temporal types, you will be able to do all of this (using date functions as required). The default output of TIMESTAMP and DATETIME types is a string in 'YYYY-MM-DD HH:MM:SS' format.

   In particular, the "elapsed time" from comparing two values could for example be obtained with MySQL's TIMEDIFF() function:

   SELECT TIMEDIFF(end, start) AS elapsed
   FROM   my_table
   WHERE  ...
   

这篇关于如何最好在MySql中存储日期/时间？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！