How to count the number of people born in EDT on September 3, 1967 when dates are stored as unix timestamps?

Question

Suppose someone from my database was born at -73440000 unix time. This means that he was born on 04- Sep-1967 UTC, but 03 -Sep-1967 EDT. How to count the number of people born on every day of the year in EDT ?

---

From the very beginning you will find that

 SELECT FROM_UNIXTIME(-73440000) -- returns NULL 

Returns NULL . MySQL cannot handle negative unix timestamps.

Ok, we can get around this:

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

Yield 1967-09-04 00:00:00 , which is his date of birth 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 turned out, CONVERT_TZ does not work in dates until 1970.

( already set time zones and it works with dates between 1970 and 2038 )

So now I'm stuck. I need to convert the unix timestamp to MySQL DATE so that I can have it GROUP BY and then COUNT. The only other option I can think of is to return each record to the database as unix timestamps and use a different language to convert and count them, but this can be a little ridiculous if there are millions of records.

NB You cannot calculate the clock offset between EDT and UTC either because it can change during the year (summer time).

Answer 1

You can do the conversion manually; if you already have zone data loaded in MySQL, then you have data that is already returning 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; 

By changing the date of your sample, 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 selects the most recent transition to the date, it does not return any records for dates preceding the first transition. This should serve as an indicator that you are in terra incognita and you will have to compensate for your own displacement.

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

Answer 2

I tested these functions with over 200 dates between 1900 1918 (before that time was messed up!) And 2100, and compared them to the w / timezones dates generated by PHP. They seem to work great.

Turn the timestamp into 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 the date and time 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 , while PHP gives 1478419200 , however both timestamps correspond to 1 : 00 AM (due to DST switching), so I’ll assume that the answer is correct.

NB These functions give null if the session time zone is not set. Install it with:

 set session time_zone='America/Vancouver';