Unix时间戳VS日期时间 [英] Unix timestamp VS datetime

问题描述

可能重复:
日期时间与时间戳记?

我有一个Mysql表，其中有一个列add_date.它跟踪记录添加到数据库中的日期/时间.

基于此表的查询:

1. 显示何时以以下格式添加记录:2小时前，4周前，1年等
2. 允许用户搜索在每天/每月/每年中插入的记录.因此，可以允许用户选择仅查看插入在2009年的记录.

在这种情况下哪个会更好-Unix时间戳还是日期时间?

现在我同时使用这两个列，但是由于该表随时间推移将具有数百万条记录，因此同时具有这两个列可能会影响数据库的大小.

Unix时间戳似乎更适合在PHP中转换为2 hours以前的格式，并且它与时区无关.但是datetime具有更好的可读性，并且使查询特定日期/时间/年份似乎更容易.

您的建议?

解决方案

如果可以选择的话，我想说说mySQL日期.

• 您不必担心日期范围问题 p>

• 您可以使用mySQL的日期函数(BETWEEN(), DATE_ADD等)轻松查询时间跨度.

• 与日期相关的查询会更快，尤其是当您有数百万条记录时，因为您不必使用FROM_UNIXTIME()，在大型查询中这可能会很昂贵

• 在必要时将DATE字段转换为UNIX时间戳是孩子的事.

Possible Duplicate:
datetime vs timestamp?

I have a Mysql table, which has a column add_date. It tracks the date/time when the record was added to the database.

Queries based on this table:

1. Display when the record was added in the format: 2 hours ago, 4 weeks ago, 1 year ago etc
2. Allow users to search records inserted in any day/month/year. So the user may be allowed to choose to see the records inserted in 2009 only.

Which would be better in this case - unix timestamp or datetime?

Right now I'm using both, but since the table will have millions of records over time, having both columns may affect the size of the database.

Unix timestamp seem to be better for conversion to 2 hours ago format in PHP and also it is timezone independent. But datetime has better readability and making a query for a particular date/time/year seems easier.

Your suggestion?

解决方案

When you have the choice, I'd say go for mySQL dates.

• You won't have to take care of date range issues

• You can easily query time spans using mySQL's date functions (BETWEEN(), DATE_ADD etc.)

• Date related queries will be much faster, especially when you have millions of records, because you won't have to use FROM_UNIXTIME() which can be expensive in large queries

• It's child's play to convert DATE fields into UNIX timestamps when necessary.

这篇关于Unix时间戳VS日期时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！