Using readable timestamps in SQL and PHP

Door chaozz op dinsdag 7 januari 2014 11:13 - Reacties (52)
Categorie: -, Views: 11.519

In a lot of cases you want to store timestamps in your database. There are a lot of ways and formats to do so, but the most commonly used method is using Unix Epoch format. I want to discuss an alternative.†Using readable timestamps in SQL and PHP.
Unix Epoch
Let's for example take the following date: the 5th of January 2014 at 01:39:12

In Unix Epoch this would be displayed as:†1388882352

This number is the number of seconds that passed since the 1st of January 1970 until the date mentioned above. While this is an efficient way of storing the date, it is useless unless you convert it into a readable date. But why not store it readable in the first place?
The readable alternative

PHP:
1
2
3
$date = new DateTime();
$now = $date->format('YmdHis');
echo $now;



Using the same date mentioned above this would generate a date that looks like this: 20140105013912.

The format is: 4 year digits, 2 month digits, 2 day digits, 2 hour digits, 2 minute digits and 2 second digits. There you have it, a <em>readable</em> date.†
Advantages
Using this timestamp format has a number of advantages:
  • First and most important, if you are doing queries directly on your database, for example from a front-end like PhpMyAdmin, timestamps stored in this format are very readable. You can instantly see what date is being displayed.
  • Just like Unix Epoch, you can still do queries sorted by date.
  • Timestamps are still comparable. You can still do $past_date < $future_date, just like with Unix Epoch.
  • You can query periods really simple, using the LIKE function from SQL
Storage
For storage in a database it's best to use the field type BIGINT, which uses 8 bytes of storage. You could also use a VARCHAR(14) field, which is the exact size you need for this date format, but this would use 14 bytes for the date + 1 byte for storing the string length. Using BIGINT saves you 7 bytes per record over VARCHAR(14).
Example 1: order by date
I have a database table that stores when my cron jobs are ran. In the example below I queried my log to see when the last crons were ran. The query:


SQL:
1
SELECT * FROM log ORDER BY date DESC;



result: http://4techs.org/wp-cont...014/01/readable-dates.png

I can instantly see in the date field the last 2 minute-cron ran at 01:42:01 on the 5th of January 2014.
Example 2: compare dates
Comparing timestamps is very easy. Lets take for example the table called logs†from the screenshot above. If I want to see all the cron jobs that were ran after yesterday 16:00:00 I can simply use the query:


SQL:
1
SELECT * FROM log WHERE date > 20140104160000;



But as mentioned you can also query using the LIKE method. For example, if you want all the logs from February 2011, you simply query:


SQL:
1
SELECT * FROM log WHERE date LIKE '201102%';



With Unix Epoch you can subtract two timestamps and the result will be the difference in seconds between those dates. If you want the number of seconds between two timestamps in the readable format you can use the following code:

PHP:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
Function SecondsDiff($fromtime, $totime) {
 $from = strtotime($fromtime); // convert readable date to unix epoch
 $to = strtotime($totime);
 $diff = $to - $from;
 if ($diff < 0) $diff = 0;
 return $diff;
}

$date = new DateTime();
$present = $date->format('YmdHis');
$date->modify('+10 minutes');
$future = $date->format('YmdHis');

$difference_in_seconds = SecondsDiff($present, $future);

Example 3: past and future timestamps
Something that is also useful is creating future or past timestamps. Let's say we have the following SQL table:

TABLE:
  • future_tasks
FIELDS:
  • id, INT(10), auto increment
  • type, VARCHAR(10)
  • date, BIGINT
The PHP script below inserts a task into the SQL table with a date set 10 minutes from the current date:

PHP:
1
2
3
4
5
$date = new DateTime();
$date->modify('+10 minutes');
$task_date = $date->format('YmdHis');

$result = msyqli_query ($link, "INSERT INTO future_tasks (type, date) VALUES ('clear_log', $task_date)");



Now let's write a cron job script that runs every minute to see if it needs to clear the log:

PHP:
1
2
3
4
5
6
7
$date = new DateTime();
$now = $date->format('YmdHis');
$result = mysqli_query ($link, "SELECT id FROM future_tasks WHERE type = 'clear_log' AND date < $now");
// Clear the log here
$result = mysqli_query ($link, "TRUNCATE TABLE log"); // clear the log efficiently
// delete task so the cron job will not be triggered again by past tasks
$result = mysqli_query ($link, "DELETE FROM future_tasks WHERE type='clear_log' AND date < $now");



I hope you find this useful for your SQL and PHP projects.