Page 1 of 2

timestamp question

Posted: Thu Apr 20, 2006 7:56 pm
by bruceg
I want to have a timestamp which shows when a record has been last updated. In my table in MySQL I have created a field with the type 'timestamp', but I am not sure how to display that on my page using PHP.

Posted: Thu Apr 20, 2006 9:41 pm
by alex.barylski
I just use a regular INT and store a TIMESTAMP created with

Code: Select all

mktime()

Posted: Thu Apr 20, 2006 11:19 pm
by bruceg
I have a table field in MySQL called last_update with the type int.

Still confused about how to get that to print on a webpage with PHP and mktime would it be mktime(last_update)?



I know how to create a last modified for a page, but still confused about a record in a database.

Posted: Fri Apr 21, 2006 1:07 am
by themurph
Mabye a variation of the code below is what you might need

From http://www.awtrey.com/tutorials/dbeweb/php.php

Where last_update in mysql is of the TIMESTAMP type:

Using the MySQL timestamp datatype in PHP requires a little coding. This next bit takes apart the MySQL data and generates a formated date. The substr function just grabs a substring from a given string. The mktime function generates a timestamp that the date function uses to generate the date and time in the format specified.

Code: Select all

<?
$datefromdb = $guest['last_update'];
$year = substr($datefromdb,0,4);
$mon = substr($datefromdb,4,2);
$day = substr($datefromdb,6,2);
$hour = substr($datefromdb,8,2);
$min = substr($datefromdb,10,2);
$sec = substr($datefromdb,12,2);
$orgdate = date("l F dS, Y h:i A",mktime($hour,$min,$sec,$mon,$day,$year));
echo "$orgdate";
?>

Posted: Fri Apr 21, 2006 6:19 am
by timvw
MySQL has a function DATE_FORMAT for a reason...

Posted: Fri Apr 21, 2006 7:55 am
by Oren
Simply create a regular INT field as Hockey said and when you INSERT the date just use the time() function.
When you want to pull the data from your database and print the date do it like this - let's say you have the timestamp in a variable called $time:

Code: Select all

echo date('[your_time_format]', $time);

Posted: Fri Apr 21, 2006 10:23 am
by hawleyjr
timvw wrote:MySQL has a function DATE_FORMAT for a reason...
:D :D

Listen to timvw...

Posted: Fri Apr 21, 2006 10:42 am
by Oren
I can't just 'listen', give few examples to support your claim and then if it sounds good I'll listen.

Posted: Fri Apr 21, 2006 10:53 am
by pickle
Read the MySQL documentation - it'll explain everything.

For example:

Code: Select all

SELECT
  DATE_FORMAT(timestampColumn,'%a %b') as 'Pretty date'
FROM
  table
Should get you results like:

Code: Select all

+------------+
|Pretty date |
|Sun Jan     |
|Mon Jan     |
|Tue Jan     |
|Wed Dec     |

...etc
However, the documentation examples show this function being used with a DATETIME stamp, not a TIMESTAMP stamp - so I'm not 100% how it'll work.
MySQL Date & Time Functions


So ~bruceg, if you've already got your table setup with the timestamp type, using the MySQL function DATE_FORMAT() is probably your best bet.

Posted: Fri Apr 21, 2006 10:56 am
by feyd
The existing column is a TIMESTAMP. There's no compelling reason to alter that at this point. Using the DATE_FORMAT() function allows the column to remain unaltered and allows the original poster to select whatever format he wishes as output. Now, if multiple databases are being supported, TIMESTAMP may not work "best." Additionally, if the stored information needs to be displayed in multiple formats at any given time, it may be better to store it as an unix timestamp, but again, MySQL has a function to convert any date-time field into a unix timestamp. For maximum portability, yes I would agree that INT is the "better" storage, but I see no reason to alter the table at this point.

Posted: Fri Apr 21, 2006 11:52 am
by timvw
Disadvantage of INT (imho) is that it's (default) only 4 bytes (32bits) and only i usuable for dates between unix epoch and somewhere 2038...

(In my experience i've always seen applications that where replaced and kept using the same dbms... but almost never a dbms was replaced in favor for another using the same application)

Posted: Fri Apr 21, 2006 1:43 pm
by bruceg
I think I am still missing quite a bit here.

In MySQL I have a field called last_updated which is set as type INT.

in PHP

Code: Select all

$sql = "SELECT last_updated
FROM $table_name WHERE id = '$_GET[id]' ";
$result = @mysql_query($sql, $connection) or die (mysql_error());
while ($row = mysql_fetch_array($result)) {
$last_updated=$row['last_updated'];

Last Update: <?php echo date('[l dS \of F Y h:i:s A]', $last_updated); ?>
this outputs:

Last Update: [Thursday 01st of January 1970 12:00:00 AM]

:-(

what am I missing? (probably a lot) also tried using type TIMESTAMP in the field.

Posted: Fri Apr 21, 2006 1:46 pm
by pickle
What's the value of $last_updated? That date is equivalent to a UNIX timestamp of 0 - which leads me to believe your query isn't getting what you think it is.

Posted: Fri Apr 21, 2006 1:53 pm
by bruceg
what should the value be? I don't have a value set in MySQL

Posted: Fri Apr 21, 2006 1:58 pm
by pickle
Your SQL query is supposed to be retrieving the last_updated column. However, it's not doing that because the date you're getting is equivalent to 0. Run your query through the CLI or phpMyAdmin to see what the results are - I'm guessing they're not what you want.