FROM_UNIXTIME() Problem

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Mo
Forum Newbie
Posts: 21
Joined: Thu Nov 06, 2003 7:21 am

FROM_UNIXTIME() Problem

Post 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
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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]
Mo
Forum Newbie
Posts: 21
Joined: Thu Nov 06, 2003 7:21 am

Post by Mo »

That did it! Thanks.
Post Reply