sql - Convert dates with mysql, Microsoft 1900 Date system -

A subsystem (which I do not have control) export data from a mysql table and store days from days to 19 00 () I need dates that come in the form of a Unix time stamp.

Example of two dates

  from SELECT the date my_table WHERE id = 1 OR id = 2 41315,3921180556 413217272013889  

Select some_magic (date) from desired output

  my_table WHERE id = 1 or id = 2 1360488279 1361035587  

I could not find a job Something is missing?

I want to select the date to be distributed as a PHP function to write.

UnMicrosoftify function ($ fDateValue = 0, $ iDateBase = 1 9 00) {if ($ iDateBase == 1900) {$ iMyDateBase = 25569; // Adjust for the fictional 29th February -1900 (60 days) if ($ fDateValue & lt; 60) {- $ iMyDateBase; }} And {$ iMyDateBase = 24107; } // If you make a conversion ($ fDateValue> = 1) {$ utcDays = $ fDateValue - $ iMyDateBase; $ IReturnValue = Round ($ utcDays * 86400); If ($ iReturnValue & lt; = PHP_INT_MAX) & amp; amp; ($ iReturnValue & gt; = -PHP_INT_MAX)) {$ iReturnValue = (integer) $ iReturnValue; }} And {$ hour = round ($ fDateValue * 24); $ Mins = Round ($ fDateValue * 1440) - Goal ($ hour * 60); $ Secs = Round ($ fDateValue * 86400) - Goal ($ hour * 3600) - round ($ mins * 60); $ IReturnValue = (integer) gmmktime ($ hour, $ min, $ sec); } Return $ iReturnValue; } Echo UnMicrosoftify (float) str_replace (',', '.', '41315,3921180556')) / 1360488279 Displays (ie 2013-02-10 09:24:39) Echo UnMicrosoftify (float) str_replace ( ',', '.,' 413217272013889 ')) / displayed 1361035587 (i.e. 2013-02-16 17:26:27)

Is it possible to do this with mysql ?

If the date is stored in varchar, then you put it in decimal of appropriate precision And then it should work as shown in the first version; If it already works in a decimal second edition.

Try it, I think this is what you want:

  SELECT date as ExcelSerialDate, ADDDATE (ADDDATE (ADDDATE ('1899-12-31 00 ('Date', ',', '.')), Interval-1 day, interval (MOD (date, 1) * 86400) Second: ConvDate (00), DCIMAL (30,12) , UNIX_TIMESTAMP (ADDDATE (ADDDATE ('1899/12/31 00:00', Dali (date of place (, ',', '.'), Decimal (3012)), form of interval In decimal (30,12), 1) * 86400) second)) UNIXTS_From_varchar, UNIX_TIMESTAMP (ADDDATE (in ADDDATE (1DAY), interval (MOD (date (instead of date (, ',', '.') ADDDATE (ADDDA) In the form of TE ('1899/12/31 00: 00', date2), interval 1day), interval (mOD (date2,1) * 86400) second)) from my_table as unixTS_from_double;  

returns this with its sample data:

  EXCELSERIALDATE CONVDATE UNIXTS_FROM_VARCHAR UNIXTS_FROM_DOUBLE 41315,3921180556 2013-02-10 00 : 00: 00 1360488279 1360488279 413217272013889 2013-02-17 00:00:00 1361121987 1361121987