Convert float day of year in datetime mysql

Question

I have the following table in MySQL DB:

year | day_of_year
2012 | 283.813090

How to convert these fields in DateTime?

I tried:

SELECT MAKEDATE(year, day_of_year) from tableName;

It returns the Date (yyyy-mm-dd)... How can I get the Time (hh:mm:ss)?

Can anyone help me solve this problem?

Answer 1

By using MySQL's SEC_TO_TIME() function (and a little math), you'll be able to get your timestamp.

The math involved would be to multiply your day_of_year column by the number of seconds in a day, 86400, and then mod-the result on 86400, giving you the equation ((day_of_year * 86400) % 86400):

SELECT SEC_TO_TIME((day_of_year * 86400) % 86400) AS time;

If I run this with the value 283.813090, it gives me the correct time-of-day*:

mysql> SELECT SEC_TO_TIME((283.813090 * 86400) % 86400) AS time;
+----------+
| time     |
+----------+
| 19:30:51 |
+----------+

To combine this with the actual date, you can either select them in two separate columns or use CONCAT() to get a real "timestamp":

SELECT
    CONCAT(MAKEDATE(year, day_of_year), ' ', SEC_TO_TIME((day_of_year * 86400) % 86400)) AS timestamp
FROM tableName;

* The math behind the time-of-day calculation is fairly straightforward. For 283.813090, this says that there are 283 days. To calculate the time-of-day, we use the fractional portion and multiply by 24 (for the hours). .813090 * 24 = 19.51416. This says there are 19 hours; to calculate minutes, we take the fractional portion of this and multiply by 60. .51416 * 60 = 30.8496, which says 30 minutes. For the seconds, again, take the fractional part and multiply by 60: .8496 * 60 = 50.976. So, altogether we have 19 hours, 30 minutes and 50 seconds (with .976 milliseconds, which rounds up) - 19:30:51, or 7:30pm.