Date Format 3

[dbadmin]

Hi Gurus,

I have a MySQL database and it has one table with a date field and the format is as below '1212246855000'. How could I convert it to a valid mm/dd/yyyy hh:mi:ss format?

Thanks,
dbadmin

 

[audiopro]

That format is 'epoch seconds' (have a Google for it)

It is a very useful format especially if passing data accross different languages. If you are using PHP there will be decoding routines already written for it. Not too sure on that as I use other languages for the same purpose.

Keith

http://www.studiosoft.co.uk

 

[r937]

unfortunately '1212246855000' is not a unix epoch time

unix epoch times run from 1970-01-01 through to some time in the year 2037 or 2038

see

http://en.wikipedia.org/wiki/Year_2038_problem

if '1212246855000' were a unix epoch time, it would actually be some 38,000 years later



r937.com | rudy.ca

 

[ingresman]

Can you do a controled insert into the table e.g. run the app and note the time and then go and have a look what the column says.
do you access to the source code ?.
audiopro is corret, mysql has some function to convert date and times. Have you looked into these at

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

Can you post the description of the table and highlight which column you have issues with, I'm curious to see know how you got the internal value .

 

[audiopro]

I will stick by my original post of it being an epoch date. The difference is that in this form it is the number of milliseconds since 1970-01-01. I vaguely remember learning this many years ago bit I cannot remember why such accuracy was required. Remove the last three digits (convert to seconds) and you get a valid date.

31 May 2008 16:14:15 GMT

Keith

http://www.studiosoft.co.uk

 

[r937]

remove the last three digits"

DOH!! why ditn i think of that!

nice one, keith, star from me

dbadmin, your function call would be:

SELECT FROM_UNIXTIME(col/1000,'%m/%d/%Y %H:%i:%S') AS d

where [tt]col[/tt] is your column


r937.com | rudy.ca

 

[dbadmin]

Thank You everyone for the answer. from_UNIXTIME fixed the issue for me.

dbadmin.