A Web Developer's Diary

Good to know: how to properly store date and time values in MySQL

6/30/2012

6 Comments

 
MySQL has two native field types that can store information about both date and time: timestamp and datetime. The major differences between timestamp and datetime field types are:
  1. timestamp can hold values only in the range '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC while datetime can hold any value in the range '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
  2. timestamp is the only mysql field type that stores values relative to a timezone (namely UTC), datetime does not store according to a time zone!

This has some serious implications. First, timestamp can only be used where the supported value range is sufficient. Second, for scenarios where you would want to log, compare or perform other tasks with date and time, datetime is not reliable if you adhere to your server time zone settings because datetime does not store the time zone information. An example to illustrate this unreliability:

Suppose you are logging transactions of some sort to your MySQL database, neatly adding the date and time at which the transaction took place, relying on the server time zone settings. Another process is monitoring the transactions (e.g. is client X not exceeding Y transactions per hour, whether transactions result in insufficient account balance, etc.). Now suppose the time zone of the server changes, this could be because of any reason:
  1. Some countries have daylight savings time, if your server is configured to that country's particular timezone, your server automatically changes time zone upon entering and leaving daylight savings time.
  2. The server administrator manually changes the time zone, e.g. because the server moved physically, perhaps the change is by accident or he/she decided it was better to change to a time zone that doesn't have daylight savings time ;-)
  3. A bug in the system software changes the time zone by accident.

To stick with the daylight savings example, let's say the clock gets rewound one hour. Transactions are still happily being inserted into the database using datetime. Now, according to the transaction log, customers are making twice the amount of transactions during this hour. But wait?! Weren't we keeping an eye on the maximum amount of transactions per hour of clients? It looks like some clients that are operating at only half of their capacity suddenly exceed the maximum transaction limit during that hour, uh oh!

How will MySQL know whether it was 2:30 AM before or after daylight saving time when you use timestamp? The answer is: MySQL doesn't know! E.g. 28 October 2012 2:30 AM in either CEST (UTC+2:00) or CET (UTC+1:00) are both stored as unix timestamp value 1351387800 (= "2012-10-28 02:30 CET" = "2012-10-28 01:30 UTC"). As we will see soon, this is documented behaviour. It's not possible to insert unix timestamps directly into a timestamp field, so you are required to use a text representation of the date and time you are trying to store (e.g. "2012-10-28 2:30", NOW(), CURRENT_TIMESTAMP). MySQL takes this string and converts it into an unix timestamp using UNIX_TIMESTAMP(), this is where the point where 28 October 2012 2:30 AM CEST and CET both get mapped to 1351387800. One suggested work-around I came accross doesn't work in my boundary case, confirming what the manual says:

 mysql> SELECT FROM_UNIXTIME(@ts := 1351387800) = FROM_UNIXTIME(@ts - 3600);
+--------------------------------------------------------------+
| FROM_UNIXTIME(@ts := 1351387800) = FROM_UNIXTIME(@ts - 3600) |
+--------------------------------------------------------------+
| 1 |
+--------------------------------------------------------------+
1 row in set (0.00 sec)

So how can we store our dates and times without being affected by this? The answer lies in designing your database (and application). Trusting your server's time zone settings and MySQL's time zone conversion abilities is a bad idea. Instead, use datetime fields and store UTC formatted values only.  You can always get the current date and time in UTC via UTC_TIMESTAMP(), e.g.:

 mysql> SELECT UNIX_TIMESTAMP(), UTC_TIMESTAMP(), NOW();
+------------------+---------------------+---------------------+
| UNIX_TIMESTAMP() | UTC_TIMESTAMP() | NOW() |
+------------------+---------------------+---------------------+
| 1351384200 | 2012-10-28 00:30:00 | 2012-10-28 02:30:00 |
+------------------+---------------------+---------------------+
1 row in set (0.00 sec)

Alternatively, you could store unix timestamps in unsigned int columns. But then you can not reliably use all those useful documented MySQL date and time functions, and, you would have to write your own date and time calculations using math in queries which can get messy.

A disadvantage of storing all values in a datetime field is that you cannot make use anymore of the CURRENT_TIMESTAMP default value. But then again, if you need to store values outside the timestamp range or need 100% reliability, that might be one of your lesser concerns.

Conclusion
Use UTC formatted date and time values within MySQL and your application for reliability    . Store all your date and time values in UTC format in a datetime column.
6 Comments

Pro-tip: when to avoid addslashes() in PHP

6/29/2012

2 Comments

 
When peer reviewing PHP code, I often find dangerous uses of addslashes(). It is often believed this is a safe way of escaping user input before passing it to e.g. a SQL query, but in fact it's unsafe. If you find yourself using addslashes(), think twice if you are using it safely:
  • In a MySQL context, use mysql_real_escape_string() instead.
  • MySQLi has an identical mysqli_real_escape_string().
  • PDO provides it's own escape method PDO::quote().
  • PostgreSQL has a wide variety of escape functions: pg_escape_literal() for values, pg_escape_bytea() for columns of type bytea, pg_escape_identifier() is used for escaping identifiers (e.g. table, field names).
  • When trying to pass user input to the command line, use escapeshellarg() and escapeshellcmd() to escape the input.
  • When displaying non-HTML user input anywhere on a webpage, always use htmlentities() or htmlspecialchars().
  • This one is a little awkward, but I've seen it before so I thought it's worth mentioning: when including user input in URLs, use urlencode() instead of addslashes()!

If you have more suggestions for safe escaping, please leave them in the comments below. Happy safe coding!
2 Comments

Add an SPF DNS record to your Google Apps domain

6/29/2012

0 Comments

 
Adding an SPF TXT-record to your domain's DNS settings helps fighting spam. However, often there is a webserver that also wants to send mail using your domain as sender. Google's suggested SPF setting is not sufficient for this. Add (or replace if it already exists) this DNS TXT-record to your domain's DNS:
v=spf1 a include:_spf.google.com ~all
The only difference with the default value suggested by Google is the extra "a" after "v=spf1". Read Google's original instructions here. This extra "a" means that all IPs in the A-records of the domain are allowed to send mail. That should include your web server because your web server its IP is set in an A-record.
0 Comments

Minifying, Combining and Caching CSS and JS with Code Ingiter

6/29/2012

0 Comments

 
So you realised all these HTTP requests are taking up a lot of page load time? No worries! You'll be up to speed in no-time.

First download the improved version of the CI plugin Carabiner from github (or download zip), originally written by Tony Dewan and now maintained by Mike Funk. Copy the config and libraries folder into your CI installation by putting them in: application/third_party/carabiner/ and add the package to application/config/autoload.php by adjusting the auto loaded packages:
 $autoload['packages'] = array(APPPATH.'third_party/carabiner'); 
Make sure the application/third_party/carabiner/config/carabiner.php configuration file is set properly (e.g. make sure the cache path exists and is writeable) and you can test your first Minify/Combine/Cache experience using this example controller:
  
class TestCarabiner extends CI_Controller {
public function index() {
$this->load->library('carabiner');
$this->carabiner->css('file1.css');
$this->carabiner->css('file2.css');
$this->carabiner->js('file1.js');
$this->carabiner->js('file2.js');
$this->carabiner->display(); // output html which loads cached files
}
}
Besides using $this->carabiner->display() you can also use $this->carabiner->display_string() to get the HTML as a string instead of outputting it directly to the browser (e.g. so you can use it in a template). You can also call carabiner directly from a Code Igniter view using $this->carabiner->display(). Another useful function call is $this->carabiner->empty_cache('both', 'yesterday') which cleans up old cache files. Full documentation is available at this page.
0 Comments

Loading webfonts of Amazon CloudFront the right way

6/29/2012

21 Comments

 
When loading webfonts of Amazon's CDN Cloudfront, a problem arises in FF and IE9. The fonts are not rendered! This is because a header is missing and these browsers refuse to render them due to security reasons. The fix is really easy, add these lines to your .htaccess file:
 <FilesMatch "\.(ttf|otf|eot|woff|svg)$">
  <IfModule mod_headers.c>
    Header set Access-Control-Allow-Origin "*"
  </IfModule>
</FilesMatch>
Then the next step is to invalidate the font files that are cached on the CDN (you can do this through the AWS control panel). Wait until CloudFront refreshes the files and voila, it will serve the webfonts with the Access-Control-Allow-Origin header set and FF and IE9 will render them normally!
21 Comments

    Author

    Blog about random challenges of a web developer.

    Archives

    April 2015
    May 2013
    January 2013
    July 2012
    June 2012

    Categories

    All
    Aws
    Cdn
    Code Igniter
    Css
    H2database
    Jamp
    Java
    Javascript
    Magento
    Maven
    Mysql
    Opencl
    Php
    Play-framework
    Quercus
    Scala
    Ubuntu

    RSS Feed

Powered by Create your own unique website with customizable templates.