[RESOLVED] annoying Timestamp

dalar

Hey guys

My host recently upgraded phpMyAdmin to version 2.8.0.2.

Until recently, I had no problems creating TimeStamps and setting them to store the current time for whenever a new record was added to the table, worked perfectly.

With this new version of phpmyadmin, the timestamp in the column is read out as 2006-07-07 02:11:07

I thought phpmyadmin was being clever here, secretly hiding the true mysql_timestamp of long numbers and rendering it into more readable form, then I realised that this is actually how the data is stored in the column.

It is giving me problems in my scripts when I want to format the data because it doesnt understand it as a propper timestamp, which is understandable.

How do I get phpmyadmin to work the way it used to with propper timestamps? Or am I going to have to actually insert the timestamps myself from the php scripts?

Thanks in advance

bradgrafelman

Be careful about your terminology here. The word "timestamp" means two different things to MySQL and PHP respectively.

Here's a quote from the MySQL manual on TIMESTAMP properties:

TIMESTAMP columns are displayed in the same format as DATETIME columns. In other words, the display width is fixed at 19 characters, and the format is YYYY-MM-DD HH:MM:SS.

Are you referring to a "timestamp" as in, the number of seconds since the Unix epoch? If so, you'd use MySQL's UNIX_TIMESTAMP() function to insert this, such as:

INSERT INTO `myTable` (`data`, `timestamp`) VALUES ('current timestamp', UNIX_TIMESTAMP())

So, when you say "the true mysql_timestamp of long numbers", you actually mean the true unix_timestamp.

dalar

brad,

It does appear as though I am confusing timestamp terminology here. Thank you very much for the link, it helped me answer my problem.

In the newer versions of MySQL the timestamp column is formatted as a DATETIME field.

So I can use the following php code now to format any timestamp from the MySQL table as follows:

date_format(timestamp, '%d-%b-%y  %H:%i') as formatted_time 
// goes inside a SQL statement before the 'from' keyword

where formatted_time is the new temporary column with a formatted date.

Thanks for your help!

🙂