How to count the # of people born on 3-Sept-1967 EDT when dates are stored as unix timestamps?

Question

Assume someone in my database was born at -73440000 unix time. This means he's born on 04-Sep-1967 UTC but 03-Sep-1967 EDT. How would I count the number of people born on each day of the year in EDT?

---

Right off the bat, you will discover that

SELECT FROM_UNIXTIME(-73440000)
-- returns NULL

Returns NULL. MySQL can't handle negative unix timestamps.

Fine, we can work around that:

select date_add('1970-01-01', interval -73440000 second)
-- returns 1967-09-04 00:00:00

Yields 1967-09-04 00:00:00 which is his birthdate in UTC.

We can try to convert this to EDT (Toronto time):

select convert_tz('1967-09-04 00:00:00','UTC','America/Toronto')
-- returns 1967-09-04 00:00:00, but should be 1967-09-03 20:00:00

But as it turns out, CONVERT_TZ doesn't work on dates prior to 1970 either.

(I've installed the timezones already and it does work on dates between 1970 and 2038)

So now I'm stuck. I need to convert the unix timestamp to a MySQL DATE so that I can GROUP BY it and then COUNT it. The only other option I can think of is to return every record in the database as unix timestamps and use another language to do the conversion and tally them up, but that could get a bit ridiculous if there's millions of records.

N.B. You can't calculate the hour offset between EDT and UTC either because that can change throughout the year (daylight savings).

Answer 1

You could do the conversion manually; if you've got the zone data loaded into MySQL already then you've got the data already going back to 1918. Let's take a look at the list:

SET @timezone = "America/Toronto";
SELECT FROM_UNIXTIME(0) + INTERVAL Transition_Time SECOND AS change_time, Offset, Abbreviation
FROM mysql.time_zone_transition t
LEFT JOIN mysql.time_zone_transition_type tt ON (
    t.Time_zone_id = tt.Time_zone_id AND
    t.Transition_type_id = tt.Transition_type_id
)
LEFT JOIN mysql.time_zone_name n ON (
    t.Time_zone_id = n.Time_zone_id
)
WHERE n.Name = @timezone
    AND Transition_time < 0
ORDER BY change_time ASC;

Modifying for your sample date, we can extract this:

SET @timezone = "America/Toronto";
SET @birthday = -73440000;

SELECT ('1970-01-01 00:00:00' + INTERVAL @birthday SECOND) + INTERVAL Offset SECOND AS offsetDate
FROM mysql.time_zone_transition t
LEFT JOIN mysql.time_zone_transition_type tt ON (
    t.Time_zone_id = tt.Time_zone_id AND
    t.Transition_type_id = tt.Transition_type_id
)
LEFT JOIN mysql.time_zone_name n ON (
    t.Time_zone_id = n.Time_zone_id
)
WHERE n.Name = @timezone
    AND Transition_time < @birthday
ORDER BY Transition_time DESC
LIMIT 1;

Since it picks the most recent transition before the date, it will return no records for dates that precede the first transition. This should serve as an indicator that you're in terra incognita and will have to make up your own offset.

If you'd like to use the first transition time as a fallback, you can remove Transition_time < @birthday from the WHERE clause and add it to the ORDER BY clause instead, as you suggested in the comments.

Answer 2

I've tested these functions with over 200 dates between 1900 1918 (prior to that timezones are messed!) and 2100, and compared them against dates w/ timezones generated by PHP. They seem to work perfectly.

Turn a timestamp into a datetime:

CREATE FUNCTION `ts2dt` (`d` bigint) RETURNS datetime
DETERMINISTIC
  BEGIN
        DECLARE `result` DATETIME;


        SELECT ('1970-01-01 00:00:00' + INTERVAL `d` SECOND) + INTERVAL Offset SECOND INTO `result`
        FROM mysql.time_zone_transition t
        LEFT JOIN mysql.time_zone_transition_type tt ON (
            t.Time_zone_id = tt.Time_zone_id AND
            t.Transition_type_id = tt.Transition_type_id
        )
        LEFT JOIN mysql.time_zone_name n ON (
            t.Time_zone_id = n.Time_zone_id
        )
        WHERE n.Name = @@session.time_zone
        ORDER BY Transition_time <= `d` DESC, Transition_time DESC
        LIMIT 1;

        RETURN `result`;
    END

Turn a datetime into a timestamp:

CREATE FUNCTION `dt2ts` (`d` DATETIME) RETURNS BIGINT
DETERMINISTIC
BEGIN
        DECLARE `result` BIGINT;

        SELECT TIMESTAMPDIFF(SECOND, '1970-01-01 00:00:00', `d`) - Offset INTO `result`
        FROM mysql.time_zone_transition t
            LEFT JOIN mysql.time_zone_transition_type tt
                ON t.Time_zone_id = tt.Time_zone_id AND t.Transition_type_id = tt.Transition_type_id
            LEFT JOIN mysql.time_zone_name n ON t.Time_zone_id = n.Time_zone_id
        WHERE n.Name = @@session.time_zone
        ORDER BY Transition_time <= TIMESTAMPDIFF(SECOND, '1970-01-01 00:00:00', `d`) - Offset DESC, Transition_time DESC
        LIMIT 1;


        RETURN `result`;
END

The only difference I found between this and PHP is that dt2ts('2016-11-06 01:00:00') gives 1478422800 for America/Vancouver whereas PHP gives 1478419200, however, both timestamps correspond to 1:00am (because of the DST switch) so I guess either answer is correct.

N.B. These functions give null if your session timezone isn't set. Set it with:

set session time_zone='America/Vancouver';