timestamp question
Moderator: General Moderators
-
bruceg
- Forum Contributor
- Posts: 174
- Joined: Wed Mar 16, 2005 11:07 am
- Location: Morrisville, NC
- Contact:
timestamp question
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
I just use a regular INT and store a TIMESTAMP created with
Code: Select all
mktime()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.
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";
?>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:
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);Read the MySQL documentation - it'll explain everything.
For example:
Should get you results like:
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.
For example:
Code: Select all
SELECT
DATE_FORMAT(timestampColumn,'%a %b') as 'Pretty date'
FROM
table
Code: Select all
+------------+
|Pretty date |
|Sun Jan |
|Mon Jan |
|Tue Jan |
|Wed Dec |
...etcMySQL 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.
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
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.
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)
(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:
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
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.
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); ?>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.
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.