Figment

How do I convert dateline fields in a MySQL database to 'real' dates?

TIA

jharris

Do you mean dateTIME fields? If so I don't think you'll really need to do any conversion, just a little formatting as they tend to take the format of YYYY-MM-DD HH:MM:SS... Are you trying to do this conversion directly in MySQL or are you dragging the data out into an application and converting it there? Or have I missed something?

cheers

Jamie...

Figment

As an example, the table has a field called dateline, type int(10) unsigned, and a sample record contains the value 1013120482 in this field.

I am trying to (manually) work out what date this represents, so that I can purge the table of all records older than nnn days

Figment

The value 1017920994 is today, if that helps any

stevencotton

[steven@moya steven]$ perl -e 'print scalar localtime(1013120482), "\n";'
Thu Feb 7 22:21:22 2002

It's in UNIX epoch time format, number of seconds since Jan 1st 1970.

Steve.

Figment

Thanks Steve - now all I need to find is a convertor to change one to the other.

legacyPete

Taking into account previous posts you could give this a try (back up before trying!!)

Create an extra column, in this case called newcolumn of type datetime

Then run this to transfer the dates accross:

update yourtable set `newcolumn` = date_add("1970-01-01", interval `olddatecolumn` second);

[Edited by legacyPete - 4/4/2002 6:19:39 PM]

[Edited by legacyPete - 4/4/2002 6:40:04 PM]

Figment

Thanks Legacypete, that is absolutely first class and works a treat

legacyPete

...we aim to please