timestamp question

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

bruceg
Forum Contributor
Posts: 174
Joined: Wed Mar 16, 2005 11:07 am
Location: Morrisville, NC
Contact:

timestamp question

Post 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.
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Post by alex.barylski »

I just use a regular INT and store a TIMESTAMP created with

Code: Select all

mktime()
bruceg
Forum Contributor
Posts: 174
Joined: Wed Mar 16, 2005 11:07 am
Location: Morrisville, NC
Contact:

Post 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.
User avatar
themurph
Forum Commoner
Posts: 76
Joined: Wed Apr 19, 2006 1:56 pm
Contact:

Post 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";
?>
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

MySQL has a function DATE_FORMAT for a reason...
User avatar
Oren
DevNet Resident
Posts: 1640
Joined: Fri Apr 07, 2006 5:13 am
Location: Israel

Post 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);
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post by hawleyjr »

timvw wrote:MySQL has a function DATE_FORMAT for a reason...
:D :D

Listen to timvw...
User avatar
Oren
DevNet Resident
Posts: 1640
Joined: Fri Apr 07, 2006 5:13 am
Location: Israel

Post by Oren »

I can't just 'listen', give few examples to support your claim and then if it sounds good I'll listen.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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)
bruceg
Forum Contributor
Posts: 174
Joined: Wed Mar 16, 2005 11:07 am
Location: Morrisville, NC
Contact:

Post 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.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
bruceg
Forum Contributor
Posts: 174
Joined: Wed Mar 16, 2005 11:07 am
Location: Morrisville, NC
Contact:

Post by bruceg »

what should the value be? I don't have a value set in MySQL
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Post Reply