Hello all!

My name is Joao, I am new here! Thanks in advance for your help

A collegue send me an MySQL database with thousands of rows.

One column called "Date_Time" set as double as values like a timestamp,

3379346291, that should be around year 2011 or so.

How can I covert this to date and time format?

Thank you

@joao

You can use the FROM_UNIXTIME function to convert and optionally format them:
SELECT FROM_UNIXTIME(date_time, '%d-%m-%Y') AS somedate FROM yourtable

Thanks!

The problem is that I need to convert them in Excel

i.e

3381350396 = (((3381350396/60)/60)/24)+DATE(1904,1,1) = 23-02-11 23:59:56
3381350416 = (((3381350416/60)/60)/24)+DATE(1904,1,1) = 24-02-11 00:00:16

I am using this conversion, but I think that it is wrong.. Because the timestamp should be from 10 minutes in 10 minutes data.. And like this is 20 seconds period.

Can someone please tell other ideas? Thanks

when I do:

SELECT From_Unixtime(Date_time, '%d-%m-%y') AS someDate FROM Teste.pavg_10m;

It gives me a column with NULL values only

Probably because that timestamp is somewhere in 2077
The max value is 2147483647

The timestamp as to be in 2010/2011...

assuming its a MySQL db then I'd suggest you import the data into Excel using an ODBC connection to the MySQL DB and make use of the MySQL date/time functions

MySQL uses the UNIX centric way iof storing date tiem informatiuon (the number of clicks since an arbitary base date of (IIRC) 01 Jan 1970


SELECT Date_format(Date_time, '%d-%m-%y') AS someDate FROM Teste.pavg_10m;

The numbers you use are not UNIX timestamps.
Excel stores date as double, i.e the days expired since 01-01-1900 or 01-01-1904, de last one is used here.

So it's seems to me that the data in the mysql table is retreived from Excel too, and Excel exports a date in seconds.

So you need to convert it to UNIX timestamp: