How to get Unix timestamp in MySQL from UTC time?

Question

If I do unix_timestamp(some_date), it internally converts some_date to UTC, whereas some_date is already in UTC. Is there any way to get the current Unix timestamp?

Edit: I need Unix timestamp from UTC time.

Answer 1

You should be able to convert it from UTC to the local timezone first using CONVERT_TZ:

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

See also: 10.6. MySQL Server Time Zone Support

Answer 2

This works fine for me:

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

Answer 3

Here's a couple other ways of doing it:

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

You can get a UNIX timestamp in MySQL from a UTC datetime value like so:

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

I made a cheatsheet here: Should MySQL have its timezone set to UTC?

Answer 5

Because of two reasons:

1. MySQL's built-in functions UNIX_TIMESTAMP and FROM_UNIXTIME take the time zone stored in @@global.time_zone which I generally won't want/be able to change, and
2. The built-ins behavior around the time of DST changes (if global time zone uses DST) results in information loss in some use cases,

my preferred solution was to write my own functions which convert to Unix timestamp and back:

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;
 IF utcstamp = 0 THEN RETURN '1970-01-01 00:00:00';
 END IF;
 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
 IF d > 0 THEN
  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
  IF d > 0 THEN
   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
    IF d > 0 THEN                                                   -- and if it's not day 0
     SET d = d + 1;                                                 -- add a day we dropped in a previous step
    ELSEIF d = 0 THEN                                               -- otherwise if it's day 0
     SET d = 365;                                                   -- then it's actually New Year's Eve
     SET y = y - 1;                                                 -- on the year before
    END IF;
   END IF;
  ELSE SET d = d + 1;
  END IF;
 ELSE SET d = d + 1;
 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 ;

You can check these functions by executing:

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

CurrentTime and Result should be the same.