Как получить метку времени Unix в MySQL от времени UTC?

Question

Если я сделаю unix_timestamp(some_date)это внутренне конвертирует some_date к UTC, тогда как some_date уже в UTC. Есть ли способ получить текущую метку времени Unix?

Изменить: мне нужна метка времени Unix от времени UTC.

Answer 1

Сначала вы сможете преобразовать его из UTC в местный часовой пояс, используя CONVERT_TZ:

UNIX_TIMESTAMP(CONVERT_TZ(some_date, '+00:00', @@global.time_zone))

Смотрите также: 10.6. Поддержка часового пояса MySQL Server

Answer 2

Это прекрасно работает для меня:

UNIX_TIMESTAMP(CONVERT_TZ("some_date", '+00:00', 'SYSTEM'))

Answer 3

Вот несколько других способов сделать это:

SELECT UNIX_TIMESTAMP(myDate)+UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(UTC_TIMESTAMP()) 
FROM myTable

SELECT UNIX_TIMESTAMP(myDate)-TIMESTAMPDIFF(SECOND, NOW(), UTC_TIMESTAMP()) 
FROM myTable

Answer 4

Вы можете получить метку времени UNIX в MySQL из значения даты и времени в формате UTC, например:

SELECT 
UNIX_TIMESTAMP(CONVERT_TZ("some_date", '+00:00', @@session.time_zone)) 
FROM `table_name`

Я сделал здесь шпаргалку: должен ли MySQL иметь часовой пояс с UTC?

Answer 5

По двум причинам:

1. Встроенные функции MySQL UNIX_TIMESTAMP а также FROM_UNIXTIME взять часовой пояс, хранящийся в @@global.time_zone который я обычно не хочу / не могу изменить, и
2. Встроенное поведение во время перехода на летнее время (если в глобальном часовом поясе используется летнее время) в некоторых случаях приводит к потере информации,

Моим предпочтительным решением было написать свои собственные функции, которые преобразуются в метку времени Unix и обратно:

      DELIMITER $$
CREATE FUNCTION UnixTimestamp(utcstamp DATETIME) RETURNS BIGINT UNSIGNED DETERMINISTIC
BEGIN
 DECLARE y INT UNSIGNED;
 SET y = YEAR(utcstamp);
 RETURN (y - 1970)          * 365 * 24 * 60 * 60 -- total seconds in full years since 1970
      + FLOOR((y - 1969) / 4)     * 24 * 60 * 60 -- add to that total seconds in extra days for each leap year
      - FLOOR((y - 1901) / 100)   * 24 * 60 * 60 -- subtract seconds for each day in a year that is divisible by 100 because those are not leap years
      + FLOOR((y - 1601) / 400)   * 24 * 60 * 60 -- but add back each day in a leap year that is divisible by 400 because those are leap years
      + (DAYOFYEAR(utcstamp) - 1) * 24 * 60 * 60 -- total seconds in full days since beginning of year
      + HOUR(utcstamp)                 * 60 * 60 -- total seconds for each full hour passed since beginning of day
      + MINUTE(utcstamp)                    * 60 -- total seconds for each full minute since last full hour
      + SECOND(utcstamp);                        -- seconds since last full minute
END$$
DELIMITER ;

DELIMITER $$
CREATE FUNCTION FromUnixtime(utcstamp BIGINT UNSIGNED) RETURNS DATETIME DETERMINISTIC
BEGIN
 DECLARE s INT UNSIGNED;
 DECLARE d INT UNSIGNED;
 DECLARE y INT UNSIGNED;
 SET s = utcstamp % (24 * 60 * 60);
 SET d = FLOOR(utcstamp / (24 * 60 * 60)) + 365 * 370 + 90;        -- days since 1600-01-01, last full leap year period
 SET y = 1600 + FLOOR(d / (400 * 365 + 97)) * 400;                 -- year is now 1600 + 400 * n, where n >= 0
 SET d = d - (400 * 365 + 97) * FLOOR(d / (400 * 365 + 97));       -- days since 1600 + 400 * n, less than 400 years
 SET y = y + FLOOR((d - 1) / (100 * 365 + 24)) * 100;              -- year is now 1600 + 400 * n + 100 * m, where 4 > m >= 0
 SET d = d - (100 * 365 + 24) * FLOOR((d - 1) / (100 * 365 + 24)); -- days since 1600 + 400 * n + 100 * m, less than 100 years
 SET y = y + FLOOR(d / (4 * 365 + 1)) * 4;                         -- year is now 1600 + 400 * n + 100 * m + 4 * l, where 25 > l >= 0
 SET d = d - (4 * 365 + 1) * FLOOR(d / (4 * 365 + 1));             -- days since 1600 + 400 * n + 100 * m + 4 * l, less than 4 years
 SET y = y + FLOOR(d / 365);                                       -- year is now 1600 + 400 * n + 100 * m + 4 * l + k, where 4 > k >= 0
 SET d = d - 365 * FLOOR(d / 365);                                 -- days since 1600 + 400 * n + 100 * m + 4 * l + k, less than a year
 IF y % 400 = 0 OR (y % 4 = 0 AND y % 100 <> 0) THEN               -- if it's a leap year
  SET d = d + 1;                                                   -- add a day we dropped in a previous step
 END IF;
 RETURN CONCAT(MAKEDATE(y, d), ' ', LPAD(FLOOR(s / 3600), 2, 0), ':', LPAD(FLOOR((s - FLOOR(s / 3600) * 3600) / 60), 2, 0), ':', LPAD(s % 60, 2, 0));
END$$
DELIMITER ;

Вы можете проверить эти функции, выполнив:

      SELECT UTC_TIMESTAMP() AS CurrentTime, FromUnixtime(UnixTimestamp(UTC_TIMESTAMP())) AS Result;

CurrentTime а также Result должно быть так же.