Bulk convert log file date time format from 12 hour clock to 24 hour clock for mySQL database

Question

I am creating a web app to analyze data from a client's custom database. I am having difficulty finding a way how to convert the client's log file entries from 12 hour clock to 24 hour clock. The Database that I am setting this up with cannot read 12 hour time format, so is displaying the time wrong.

Log files I am using look like this:

Site_Name,number_of_clicks,DD/MM/YYYY_2:00PM,Interaction_Type

I need to convert the log files to look like this:

Site_Name,number_of_clicks,DD/MM/YYYY_14:00,Interaction_Type

There are tens of thousands of enteries per log file, so there is no way this can be done per entry. I need to figure out a way to bulk convert the entries to 24-hour clock for each of the log files.

Any help would be greatly appreciated!

Thanks!

Aaron

[What have you tried?](http://mattgemmell.com/2008/12/08/what-have-you-tried/) See [about Stack Overflow](http://stackoverflow.com/about). — John Conde, Jun 04 '13 at 04:07
I have tried nothing as of yet. Log files are being to delivered to us from ms sql server (client's DB), and our DB is running on mysql. — Aaron, Jun 04 '13 at 05:51

peterm · Answer 1 · 2013-06-04T04:41:27.980

If you want to do that on db side and you use MySql you can read it with STR_TO_DATE() and either use it as a datetime value

INSERT INTO log (`Site_Name`, `number_of_clicks`, `date`, `Interaction_Type`)
SELECT 'site1', 10, STR_TO_DATE('10/05/2013_2:00PM', '%d/%m/%Y_%l:%i%p'), 1;

or

UPDATE log
   SET date = STR_TO_DATE(str_date, '%d/%m/%Y_%l:%i%p');

assuming that date column is of type datetime, and str_date column contains string values in 12h format.

or if for some reason you store it as VARCHAR and really need to format it as DD/MM/YYYY_14:00 then you can use DATE_FORMAT()

DATE_FORMAT(STR_TO_DATE('10/05/2013_2:00PM', '%d/%m/%Y_%l:%i%p'), '%d/%m/%Y_%k:%i')

which will produce

|          NEWDATE |
--------------------
| 10/05/2013_14:00 |

To update in-place

UPDATE log
   SET str_date = DATE_FORMAT(STR_TO_DATE(str_date, '%d/%m/%Y_%l:%i%p'), '%d/%m/%Y_%k:%i');

Here is SQLFiddle demo.

Thank you! I will try with that and post the results – Aaron Jun 04 '13 at 04:58 — Aaron, Jun 04 '13 at 04:58

score 0 · Answer 2 · answered Jun 04 '13 at 12:17

Why don’t you write a simple app that will search through all log entries and update whenever it finds date in PM format. You basically need to write a piece of code that will add 12 hrs if there is PM in the time.

Here is a rough code in C#... not fully tested but you can build on this…

protected string ConvertToPM(string logEntry)

{
    string result = string.Empty;

//DD/MM/YYYY_2:00PM
if (logEntry.Contains("PM"))
{
   string temp = logEntry.Substring(logEntry.IndexOf("_"), logEntry.IndexOf(":") - logEntry.IndexOf("_"));

    result = logEntry.Replace(temp, (Convert.ToInt32(temp) + 12).ToString());
}

return result;
}

Just add a piece of code that will a) read log file line by line b) extract date part c) use function similar to the one above to alter the data…

Bulk convert log file date time format from 12 hour clock to 24 hour clock for mySQL database

2 Answers2