Page 1 of 1

FROM_UNIXTIME() Problem

Posted: Thu Nov 06, 2003 7:21 am
by Mo
I cannot seem to get the time thing right...

I am using the following to convert my timestamp:

FROM_UNIXTIME(timestamp,'%M %D, %Y - %r')

When I do this I get the wrong date.
For the timestamp: 20031104110024
I get the following date: March 31st, 1950 - 11:51:20 PM

Code: Select all

<?php

   // Get connection variables
   include('config.inc.php');

   // Connect to database and go to correct database
   $db = mysql_connect ($host, $username, $password) or die ('I cannot connect to the database because: ' . mysql_error());
   mysql_select_db ($database, $db) or die ("<b>Database $database does not exist</b>");

   $query = "SELECT ID, title, content, FROM_UNIXTIME(timestamp,'%M %D, %Y - %r') AS time FROM $table WHERE ID=1";

   $result = mysql_query($query, $db) or die ($query .': '.mysql_error()); 
  
   while ($row = mysql_fetch_array ($result)) {
    print ("<TR ALIGN=CENTER VALIGN=TOP>\n");
    print ("<TD ALIGN=CENTER VALIGN=TOP>$row[title]</TD>\n");
    print ("<TD ALIGN=CENTER VALIGN=TOP>$row[ID]</TD>\n");
    print ("<TD ALIGN=CENTER VALIGN=TOP>$row[content]</TD>\n");
    print ("<TD ALIGN=CENTER VALIGN=TOP>$row[time]</TD>\n");
    print ("</TR>\n");
   }

   mysql_close(); 
 ?>
Thanks

Posted: Thu Nov 06, 2003 8:05 am
by Weirdan
Timestamp you provided isn't expressed in seconds since 00:00 January 1st 1970. Unixtime is always expressed in a such way.

Your timestamp is just concat(year,month,day,...and so on).
To format it use DATE_FORMAT instead of FROM_UNIXTIME

[edit]
Actually TIMESTAMP(14) column internally stores the time in `unix timestamp` format, but it automatically converted in YYYYMMDDHHMMSS upon retrieval.
[/edit]

Posted: Thu Nov 06, 2003 9:32 am
by Mo
That did it! Thanks.