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.