Timestamps Crash Course
Listed In PHP and MySQL » General Development — Viewing Full TutorialWhat is a Unix Timestamp?
A Unix Timestamp is a numerical representation of how many seconds have passed since January 1st, 1970. It can be accessible through PHP's time() function, and is a universal measurement of time.
How is it useful?
Since this is time measured in seconds, and is very precise, it can be used for all sorts of functions. An example that is documented on this site is the "how long ago" function which splits the difference in seconds into appropriate periods of time (e.g. days, hours, minutes). Infact, using a Unix Timestamp to store dates in a database or file can completely replace the way that some people store their dates or timestamps in dd/mm/yy format. As Unix Timestamps are purely in numbers, the possibilities become endless and the amount of mathematical operations you can do on the numbers to find out all sorts of information is staggering. Of course, I'm not going to even bother listing them all here.
Getting a timestamp
This can be done through both PHP and MySQL, both methods are very straightforward.
Firstly, I'm going to show you how to do it in PHP - the easiest way. It can be done by using the time function and not declaring any arguments, like so:
<?php
$unix_time = time();
print $unix_time; // The current unix time, in seconds.
?>
In MySQL, you can do this and have extra functionality without this having to be parsed through PHP.
First of all, in your table, add a column (or field) with data type, TIMESTAMP property.
You can use the following lengths:
14 - Year (4) Month (2) Day (2) Hour (2) Minute (2) Second (2)
12 - Year (2) Month (2) Day (2) Hour (2) Minute (2) Second (2)
10 - Year (2) Month (2) Day (2) Hour (2) Minute (2)
8 - Year (4) Month (2) Day (2)
6 - Year (2) Month (2) Day (2)
4 - Year (2) Month (2)
2 - Year (2)
The most sensible numbers are either 6, 8, 12, or 14.
SQL commands when inserting data into the field are as follows:
NULL - Self explainitory; nothing.
NOW() - The current timestamp.
CURRENT_TIMESTAMP - Current date and time.
When using SQL you must use one of these functions. Anything else will count as NULL, and won't be too useful.
Here's an example query you could use for inserting data into a TIMESTAMP field:
INSERT INTO test_table (timestamp) VALUES ('NOW()')
Now, assuming that the timestamp column was set to TIMESTAMP(8) and I inserted this on 10th March 2006, when I select the field and print it, it will show:
06/03/10
Got that? If you're using PHP to retrieve the results, do the good old mysql_result or mysql_fetch_assoc.
Converting
"Great! I have a timestamp. Now how do I turn it into something remotely useful for my application?" - Confused person
It's very simple. If you're going to convert the timestamp with PHP, you can do it like this:
<?php
$timestamp = time(); // The timestamp that we're about to convert.
$converted = date("d/m/y",$timestamp);
echo $converted; // Show it in day/month/year format.
?>
With the MySQL stored timestamp, all you have to do is select the row and then print it. I've already shown that method above.
Which method is the best?
Well, it depends on how you want to apply the timestamp into your application. However, I would normally use PHP's function and store it in a MySQL text field. Of course, if you're a flatfile person, you can easily store it as you would do with any other data. Why you ask? I'll answer. PHP is a programming language, and is very good at manipulating numbers. MySQL on the other hand is a database program, but I must say it is very good for storage of this sort of thing.
Summary
Evaluating this, you should use PHP's timestamp support for applications where you want to display the date in simple form somewhere, and then in a more detailed format elsewhere, maintaining the numerical advantage to turn it into more interesting functions. For MySQL, you can use this for basic news systems showing the date in a basic form.
I hope this tutorial helps, post any comments below and I'll see if I can answer any additional questions. Cheers, Will.
