If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > MySQL > Timestamp to Date

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Feb 2013
Posts: 7
Timestamp to Date

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
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Mar 2013
Location: Breda, Netherlands
Posts: 15
@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
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Feb 2013
Posts: 7
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
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Feb 2013
Posts: 7
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
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Mar 2013
Location: Breda, Netherlands
Posts: 15
Probably because that timestamp is somewhere in 2077
The max value is 2147483647
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Feb 2013
Posts: 7
The timestamp as to be in 2010/2011...
Reply With Quote
  #7 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 12,217
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;
__________________
I'd rather be riding my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Mar 2013
Location: Breda, Netherlands
Posts: 15
Quote:
Originally Posted by joaompc View Post
The timestamp as to be in 2010/2011...
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:
Code:
SELECT FROM_UNIXTIME(3381350416 - (DATEDIFF('1970-01-01', '1904-01-01')*24*3600))
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On