Results 1 to 8 of 8
  1. #1
    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

  2. #2
    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

  3. #3
    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

  4. #4
    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

  5. #5
    Join Date
    Mar 2013
    Location
    Breda, Netherlands
    Posts
    15
    Probably because that timestamp is somewhere in 2077
    The max value is 2147483647

  6. #6
    Join Date
    Feb 2013
    Posts
    7
    The timestamp as to be in 2010/2011...

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,353
    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;
    http://i792.photobucket.com/albums/y.../miniDGR-0.jpg I've taken part in the Distinguished Gentlemans ride, and although I enjoyed the day the main focus was to raise awareness of Prostrate Cancer and raise some funds for research. Many thanks to the many sponsors who helped me raise some 360 towards this cause, its much appreciated and rather humbling.

    http://www.gentlemansride.com/rider/healdem

  8. #8
    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))

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •