Advice needed please Date stored as integer in mysql

Hi there i have come across a problem which I need help on please, if anyone knows of a solution it would be much appreciated ..

When I export the user data out of modx mysql data base to an excel workbook, some of the fields mainly the Date of Birth field is exporting as an integer so I have lots of data that I cant use unless I can convert it back into an actual date format.

If any one has a solution to this it would be great.

Some of the actual data
93398400
277948800
1201737600
-202435200
-164246400
1223683200
309484800
-331862400
1202515200
261705600
284688000
-469152000

Quote from: no1son at Jan 23, 2009, 11:44 AM

Hi there i have come across a problem which I need help on please, if anyone knows of a solution it would be much appreciated ..

When I export the user data out of modx mysql data base to an excel workbook, some of the fields mainly the Date of Birth field is exporting as an integer so I have lots of data that I cant use unless I can convert it back into an actual date format.

If any one has a solution to this it would be great.

Some of the actual data
93398400
277948800
1201737600
-202435200
-164246400
1223683200
309484800
-331862400
1202515200
261705600
284688000
-469152000

Are you using the "CSV for MS Excel " option in PhpMyAdmin (or one of the other Excel options) to export the data?

This one is confusing to me as well... why does MODx store dates as integers instead of MySQL’s built-in DATETIME and TIMESTAMP data types? MySQL has some really handy date handling functions... storing the data as integers makes those functions a little bit harder to reach...

This one is confusing to me as well... why does MODx store dates as integers instead of MySQL’s built-in DATETIME and TIMESTAMP data types? MySQL has some really handy date handling functions... storing the data as integers makes those functions a little bit harder to reach...

No, storing it as an integer is better , you can just retrieve it like this for instance :-
SELECT from_unixtime(date) from sometable WHERE ....
or any other function you like, you can also do easier later than or earlier than comparisons as > and < on the integer values.
It’s also easier to export these. Although having said that there is a value in using TIMESTAMP types for data that you where you need to know the actual time of the DB update.

Yes, if the app really needs dates like this you’re correct of course but most of the time things like ’published_on’, ’edited_on’. ’logged in time’ etc. don’t need this. I guess what I’m saying is use integers or TIMESTAMP as a norm, only use DATETIME where you really need it, birthdates as you quoted are an example here.

All of the date/time-related fields in the MODx core will be converted to DATETIME or TIMESTAMP MySQL types (as appropriate) for Revolution 2.1. This was not changed in 2.0 to prevent massive problems with migration of existing data from a legacy MODx site.

BTW, negative timestamps do work in most PHP 5 configurations, but if you are stuck in PHP 4 or early 5 releases, you will not be able to deal with them properly.

Thanks for clarifying, Jason. I can see the reasoning behind using integers, but for me it was a bit of a shock... I’ve used a lot of MySQL date functions and they are really flexible. Of course, you can convert the integer into a MySQL date object... but I’d have to look up the function, whereas most MySQL users will be very familiar with DATE(), YEAR(), NOW() DATE_DIFF() and the other myriad date functions MySQL offers.

Wow all seems a little confusing to a newbie like me ..lol


I exported the data to ms excel 97 to 2003

The first column is the actual stored data in mysql .. the second column is the date its meant to be,  but I looked for a converter in excel and the one I tried was =DATE(1970,1,1)+D2/86400 but this doesn&#146;t seem to work and returns incorrect dates back &#133; if anyone would have an idea what formula would work it would be great.  Mike  :’(

93398400 17/12/1972
277948800 23/10/1978
1201737600 31/01/2008
-202435200 03/08/1963
-164246400 18/10/1964
1223683200 11/10/2008

Ok found a solution to my problem many thanks to Lee over at Blend Creative for the advice.. it works a treat for me so here it is for anyone else having this problem.


1) Select that table in phpmyadmin (cms_web_user_attributes)
2) Then hit the SQL button
3) Enter this:
SELECT *, FROM_UNIXTIME(dob) as new_dob FROM `cms_web_user_attributes` WHERE 1
4) This will show all users, with an additional column on the far right called new_dob, this shows the DOB in a readable format.
5) Click on the Export button at the very bottom of the page and save to desktop with all the data you need.


Done.