Datetime conversion

ThomasDK

Hello!

I got a small (I hope 🙂) problem. In my MySQL database I got a column of dates. The dates is in this format:

02/21/2005 03:48:22 PM

Does anyone know how I can either convert this to unix_timestamp or Y-m-d H:i:s (24 hrs format)

Thanks!!

/Thomas

devinemke

what column type are you using? that does not look like a native mySQL date (YYYY-MM-DD HH:MM:SS). if you are using the proper DATE or DATETIME column type then you can use any number of mySQL's Date and Time Functions.

ThomasDK

It's a standard varchar. The data is dumped from a simple text file log and inserted into MySQL.

Maybe that means I can't do anything with MySQL on it, or?

For PHP then, is there any possibilities there? I would rather not make split()-functions for every row to get the data I want 🙁

Thanks!

leatherback

if I were you, I'd create a new column, of the proper timestamp type, and conbvert the colum to time. THen, in your application you do not need to convert it. Also, it makes more snese to store date in a date column. It will allow you comparison of dates, find records older then etcetc

devinemke

Originally posted by ThomasDK
The data is dumped from a simple text file log and inserted into MySQL.

by you? if so then tweak your file parsing script to format the dates into mySQL dates. this can be easily accomplished using:
[man]strtotime[/man]
[man]date[/man]

ThomasDK

I see that creating a new column with the correct timestamp type would solve my problem, but how will I be able to import data into this column?

The data is first dumped from an external program. I do not have the possibility to in the program change the way the dump is made. It's a simple checkbox "Logging: ON/OFF" - it's not one of my own PHP scripts.

Appreciate your help guys!

/Thomas

ThomasDK

I've done a function which first uses split to separate all the alues, then rearrange them to the order I want. Then update the database accordingly but it sure doesn't look like a nice way to solve the problem.

Last attempt, does anyone else know of a better solution to do this?

Thanks!
/Thomas