View Full Version : MySQL > PHP Timestamp

01-11-2006, 11:15 PM
ok heres the problem, i have made a MySQL Timestamp through phpMyAdmin and now im stuck, i know how to echo this onto my page but how do i customise it so its not just a bunch of turd?

Kid Charming
01-11-2006, 11:21 PM
Use MySQL's DATE_FORMAT() (http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html) function.

01-11-2006, 11:31 PM
can you help me with this please... i have it stored in


i dont understand how to change the numbers into letters etc... the website is kinda confusing

01-12-2006, 03:17 PM
Alot of people prefer to use an integer time as it is easy manipulated using PHP's date() function.


Kid Charming
01-12-2006, 04:54 PM
Assuming you want your date to read out like 'January 12, 2006':

DATE_FORMAT(yourdatecolumn,'%M %e, %Y') AS mydate

When you retrieve mydate, it will already be formatted, and you don't have to do any PHP processing. The doc page I linked to earlier lists all the formatting codes you can use.

I wouldn't suggest storing your dates as a UNIX timestamp. MySQL has a lot of very useful date and time functions that won't work on them. And if you ever really need a unix timestamp for something, there's a function to convert.

01-12-2006, 08:32 PM
ok im confused... is this right? it doesnt display anything

$ts = mysql_query("SELECT DATE_FORMAT(timestamp,'%M %e, %Y') AS mydate FROM news");
$tsr = mysql_fetch_array($ts);
$timestamp = $tsr['timestamp'];

Kid Charming
01-12-2006, 08:46 PM
When using functions on selected columns, you should alias the column for ease of retrieval. In the previous code, the formatted date column has been aliased to 'mydate', so that's the string you need to use to retrieve it in PHP.

$ts = mysql_query("SELECT DATE_FORMAT(timestamp,'%M %e, %Y') AS mydate FROM news");
$tsr = mysql_fetch_array($ts);
$timestamp = $tsr['mydate'];

Note that you can alias a function's results as the original column name. The following will also work:

$ts = mysql_query("SELECT DATE_FORMAT(timestamp,'%M %e, %Y') AS timestamp FROM news");
$tsr = mysql_fetch_array($ts);
$timestamp = $tsr['timestamp'];

01-12-2006, 09:19 PM
thanks for the help... just 1 more problem, how do i set my mysql to auto insert the current timestamp when the new row is created?

Kid Charming
01-12-2006, 09:41 PM
What version of MySQL are you using?

01-12-2006, 10:33 PM
im not sure, i just used NOW() and it seemed to work but the time is wrong... how can i change this?

Kid Charming
01-12-2006, 10:48 PM
Please run a 'SHOW CREATE TABLE yourtablename' query and post the result. A 'SHOW VERSION()' will help, too.

[Edit]Oops. SHOW VERSION() should be SELECT VERSION().

01-12-2006, 11:05 PM
`id` int(11) NOT NULL auto_increment,
`title` varchar(200) NOT NULL default '',
`post` longtext NOT NULL,
`author` varchar(35) NOT NULL default '',
`timestamp` timestamp(14) NOT NULL,
`ip` varchar(80) NOT NULL default '',
`visible` char(1) NOT NULL default '1',

i dont know the version but its likley to be new as its paid hosting

Kid Charming
01-12-2006, 11:34 PM
Nope, you're on an older version -- probably 4.0, but maybe as low as 3.23. You can find out for sure by running a 'SELECT VERSION()' query (not SHOW, as I typed earlier).

In either version, your timestamp field will automatically set itself to the server's current time when a row is created or modified; you do not need to specifically set it to NOW(). If that time is wrong, than it's a problem with the server's time (though it may mean the server's in a different timezone).

01-13-2006, 03:50 PM
indeed, my server is located in the USA, im in the UK. is there a way of dragging the '1029482612' number string onto a php document and then editing it so i add like 4 hour's and and x minutes?

01-13-2006, 04:05 PM
You can use strtotime to add time

$date1 = 1029482612;
$date2 = strtotime("+ 5 hours 30 minutes", $date1);

echo date("d/m/Y H:i:s", $date2);

01-13-2006, 04:15 PM
didnt work. does that atchually change my MySQL timestamp into php? Displayed as "31/12/1969 17:59:59"

Kid Charming
01-13-2006, 04:57 PM
MySQL's CONVERT_TZ function will do this for you:

DATE_FORMAT(CONVERT_TZ(timestamp,'GMT','EST'),''%M %e, %Y') AS mydate

There's a doc page on acceptable ways to enter your time zone identifiers here (http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html).

01-13-2006, 06:31 PM
ok so i used this code:

$ts = mysql_query("SELECT DATE_FORMAT(CONVERT_TZ(timestamp,'GMT','EST'),''%M %e, %Y') AS timestamp FROM news WHERE id = $news_id");
$tsr = mysql_fetch_array($ts);
$timestamp = $tsr['timestamp'];

and it displayed this error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/toasted/domains/angrypirate.co.uk/public_html/band/login/news.php on line 59

This is assuming that the SQL query is set to work under table news and column timestamp displaying the result for the row id set as $news_id...

is there somthing wrong with the code?

01-13-2006, 06:32 PM
Version: 4.0.17-standard

Kid Charming
01-13-2006, 07:05 PM
I typed an extra apostrophe, and you copied it into your code; that's probably the problem.

When running queries in PHP, the following construct can be very useful when debugging:

$query = "SELECT foo FROM bar";
$result = mysql_query($query) or die(mysql_error());

If your query causes a database error, mysql_query() will return FALSE. If that happens, the die() function will kill your script at that point and return the result of the mysql_error() function, which will give you the database's error message.

01-14-2006, 02:14 PM

$SQL = "SELECT DATE_FORMAT(CONVERT_TZ(timestamp,'GMT','EST'),'%M %e, %Y') AS timestamp FROM news WHERE id = $news_id";
$RESULT = mysql_query($SQL) or die(mysql_error());
$timestamp = $SQL['timestamp'];

im displayed the error:

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(timestamp,'GMT','EST'),'%M %e, %Y') AS timestamp FROM news WHE

Can someone help please.... is my version too old for this code?

Kid Charming
01-14-2006, 05:15 PM
Yep, it looks like CONVERT_TZ() is from 4.1+. One of the drags of the new 5.1 manuals online is that they no longer list which functions were introduced in 4.1. Which I'll use as segue to mention that you might want to consider getting a host that's a little more, erm, up to date (MySQL's current production version is 5.0, but there's really no good reason not to be at 4.1 by now).

That aside, we'll have to make timezone changes manually for 4.0:

AS timestamp

That should work for you. Note that I completely made up the 7 -- be sure and change that to whatever the time difference really is.

01-15-2006, 12:32 AM

The above is wrong. how do i add in more spacific's? (works with just hour input)

Kid Charming
01-15-2006, 12:40 AM
There's a chart on the function's doc page (http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html) that lists the various interval expressions.

01-15-2006, 02:39 AM
i can understand that and thats how i managed to create the above in my query but it displays as wrong... the website doesnt explain how to overcome this problem (adding in more than just hour input)

can you help?

Kid Charming
01-15-2006, 02:56 AM
the website doesnt explain how to overcome this problem (adding in more than just hour input)

Yes, it does. From the page I linked:



mysql> SELECT DATE_SUB('1998-01-01 00:00:00',
-> INTERVAL '1 1:1:1' DAY_SECOND);


If you specify an interval value that is too short (does not include all the interval parts that would be expected from the type keyword), MySQL assumes that you have left out the leftmost parts of the interval value. For example, if you specify a type of DAY_SECOND, the value of expr is expected to have days, hours, minutes, and seconds parts. If you specify a value like '1:10', MySQL assumes that the days and hours parts are missing and the value represents minutes and seconds. In other words, '1:10' DAY_SECOND is interpreted in such a way that it is equivalent to '1:10' MINUTE_SECOND.

So you need to use the DAY_SECOND interval type and leave off the day , since you only need hours, minutes, and seconds.

01-15-2006, 11:00 AM
oooooh, now i understand it. before i didnt realise you could substitute 'timestamp'.

Finally got it to work with:

SELECT DATE_FORMAT(DATE_SUB(timestamp, INTERVAL '-0 5:58:37' DAY_SECOND),'%d/%m/%y - [ %T ]') AS timestamp FROM news WHERE id = $news_id

Thanks for all your help !! :)