将MySQL UTC日期时间转换为UNIX时间戳 [英] Convert MySQL UTC datetime to UNIX timestamp

问题描述

这两个都正确返回当前的UNIX时间戳:

These both correctly return the current UNIX timestamp:

SELECT UNIX_TIMESTAMP(LOCALTIMESTAMP()); #MySql
echo time(); //PHP

但是我将UTC_TIMESTAMP存储在我的数据库中(而不是LOCALTIMESTAMP).

But I'm storing UTC_TIMESTAMPs in my database (not LOCALTIMESTAMPs).

如何使用MySQL将UTC日期时间转换为UNIX时间戳?

How can I convert a UTC datetime to a UNIX timestamp using MySQL?

推荐答案

请注意，LOCALTIMESTAMP()是NOW()的同义词.因此，您真正要问的是如何获取当前时间并将其转换为GMT，然后转换为unix时间戳以存储在数据库中.这样就可以了:

Note that LOCALTIMESTAMP() is a synonym for NOW(). So what you're really asking is how to get the current time and convert it to GMT and then convert to a unix timestamp to store in the db. So this will work:

SELECT UNIX_TIMESTAMP(CONVERT_TZ(NOW(), @@global.time_zone, 'GMT'));

顺便说一句，使用数据库的时间和日期列总比使用unix时间戳总要好得多.它使查询和显示结果变得更加容易.

As an aside, it's always much better to use the time and date columns of a database rather than unix timestamps. It makes querying and displaying results much easier.

更新:您确定您正在得到自己的想法吗? UNIX_TIMESTAMP返回基于UTC的秒数，因为UNIX时代.它不会返回 MySQL DateTime类型.如果您有实际的UTC DateTime实例，则可以将其直接放入数据库的DateTime列中，而不必使用UNIX_TIMESTAMP作为中介.您实际在当地时间使用的是哪种类型?

Update: Are you sure you are getting what you think you are? UNIX_TIMESTAMP returns a UTC based seconds since the UNIX epoch. It does not return a MySQL DateTime type. If you have an actual UTC DateTime instance, then you can put that directly into your DateTime column of your database and don't have to use UNIX_TIMESTAMP as an intermediary. What type do you actually have that's in local time?

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