...

View Full Version : MySQL > PHP Timestamp



danielwarner
01-11-2006, 10: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, 10:21 PM
Use MySQL's DATE_FORMAT() (http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html) function.

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


$timestamp;

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

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

http://php.codenewbie.com/articles/php/1488/Converting_MySQL_Timestamp_to_a_Unix_Timestamp-Page_1.html

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



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


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.

danielwarner
01-12-2006, 07: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, 07: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'];

danielwarner
01-12-2006, 08: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, 08:41 PM
What version of MySQL are you using?

danielwarner
01-12-2006, 09: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, 09: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().

danielwarner
01-12-2006, 10:05 PM
CREATE TABLE `news` (
`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',
PRIMARY KEY (`id`)
) TYPE=ISAM

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

Kid Charming
01-12-2006, 10: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).

danielwarner
01-13-2006, 02: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?

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


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

echo date("d/m/Y H:i:s", $date2);
?>
http://uk2.php.net/manual/en/function.strtotime.php

danielwarner
01-13-2006, 03: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, 03:57 PM
MySQL's CONVERT_TZ function will do this for you:



SELECT
DATE_FORMAT(CONVERT_TZ(timestamp,'GMT','EST'),''%M %e, %Y') AS mydate
FROM
etc.


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

danielwarner
01-13-2006, 05: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?

danielwarner
01-13-2006, 05:32 PM
Version: 4.0.17-standard

Kid Charming
01-13-2006, 06: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.

danielwarner
01-14-2006, 01:14 PM
Using:

$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, 04: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:



SELECT
DATE_FORMAT(DATE_SUB(timestamp, INTERVAL 7 HOUR),'%M %e, %Y')
AS timestamp
FROM
...


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.

danielwarner
01-14-2006, 11:32 PM
INTERVAL -6 HOUR 1 MINUTE 23 SECOND

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

Kid Charming
01-14-2006, 11:40 PM
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.

danielwarner
01-15-2006, 01: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, 01: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:



DAY_SECOND | 'DAYS HOURS:MINUTES:SECONDS'

...

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.

danielwarner
01-15-2006, 10: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 !! :)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum