Converting MySQL datetime to very specific format for export

Question

I need to convert data from a MySQL to a very specific datetime format so that it can be automatically integrated into a client's database on a regular basis.

I would like to know if there is a way to convert the usual MySQL timestamp in format YYYY-MM-DD hh:mm:ss (e.g. 2012-01-10 10:58:07) to a very specific string that describes the date/time format like dd-Mos-yy hh:mm:ss.sss (e.g. 07-Sep-10 12:00:00.000). The new date/time format has to be exact, down to the millisecond values.

I know there are some clever ways to manipulate incoming data to put a string into the datetime format MySQL can accept, but I cannot seem to find the same tools for exporting data. I would like to handle most of this using SQL, but have some knowledge of PHP if that is a better tool to use.

Answer 1

Just use DATE_FORMAT() when you are creating the MySQL query:

SELECT DATE_FORMAT(date, '%d-%b-%y %H:%m:%s.%f') as date_formatted FROM your_table;

This handles microseconds as well using the %f option but that provides 6 digits & not 3 digits as your question specs out. If you genuinely know the date will need 3 digits for the milliseconds, you can use SUBSTRING() to just return the string date data you need. This should work

SELECT SUBSTRING(DATE_FORMAT(date, '%d-%b-%y %H:%m:%s.%f'),1,22) as date_formatted FROM your_table;

But you can also fudge 3 digits by just explicitly setting 3 0’s after the time like so:

SELECT DATE_FORMAT(date, '%d-%b-%y %H:%m:%s.000') as date_formatted FROM your_table;

Note how the %f is now .000. But that said, that is not millisecond precision since the .000 is simply appended as text & not based on real data. At least %f is working with real date data.

So if based on the examples above DATE_FORMAT is not 100% perfect for your needs perhaps using PHP date formatting would be the best way to handle. If that is the case, the date/time data would be stored as a Unix timestamp? Or as just a timestamp?

If it’s just a timestamp, using UNIX_TIMESTAMP() would convert it:

SELECT UNIX_TIMESTAMP(date) as date_unixtimestamp FROM your_table;

Then take that date_unixtimestamp and format as you wish in PHP.

Answer 2

Where will you get the millisecond information from if you didn't have it already? You need to set the milliseconds to '000' - hardcoded. Now its simple like that:

echo date('dd-Mos-yy hh:mm:ss', strtotime($mysql_time)) . '000';

I would also make sure, that the other database server is using the same timezone as you. Otherwise you might have to convert the time before.