Dates in Laravel/PHP and MySQL

OK, so there are many, many posts out there discussing dates in MySQL and PHP, but I’m doing one more to explain what I’m going to do with my Laravel app and why.

Some background on my web app:

I want to record the time when things happen. So if a user clicked a Save button on Wednesday afternoon, I want to be able to tell them what time and on what day.

I’m in Australia but the server in the USA and users could be all over the world. So if Alice shares something at 10am in London, I want Bob in Sydney to see it as 8pm  and Carol in Boston to see 6am in Boston (or whatever), adjusted for daylight savings if required. So the time display must be based on the timezone of the user or their browser, not the timezone of the server.

I’m using Laravel 4.2, which contains the Carbon date / time library. (I might add Carbon to my aliases so I can use it more easily.)

And I’m using Facebook logins which give me the user’s current timezone offset. I don’t get the actual timezone itself, but the offset is good enough for my purposes. (Many thanks to Maks Surguy for his excellent Laravel Facebook login guide.)

Some background on the date formats involved:

PHP natively uses Unix timestamps, which is simply the number of seconds since 1 January 1970 (UTC).

One MySQL format is datetimes, which are more or less the same as text strings eg 2014-11-30 23:30:00 with no timezone information in them at all[1].

The other MySQL format is MySQL timestamps (which are nothing at all like PHP’s Unix timestamps). Since MySQL 5.6.5 they are basically the same as datetimes but with a smaller range and automatic timezone  changes based on the server’s timezone (not the browser’s). So I won’t be considering them any further.[2]

Some options are:

1 – Use integers / Unix timestamps all the way

Save it as an Integer in MySQL and process it as an integer in PHP until you actually render it on your HTML page, when you adjust it for the user’s timezone offset and format it however you want using a PHP function like date().

Pros: Simplicity. No conversions required.

Cons: If you look at the data in the database, it’s stored as an integer (or longint) so you can’t immediately tell what date / time it is. Also it’s not obvious from the Unix timestamp integer which timezone it’s in.

2 – Let MySQL do the conversions

Save it in the database as a MySQL datetime, but load it from the table like SELECT UNIX_TIMESTAMP(datetimefield) FROM table WHERE... and save it to the table like UPDATE table SET datetimefield = FROM_UNIXTIME($phpdate) WHERE...

Pros: You can see in the database that it’s a date and time, and what date/time it is.

Cons: Non-trivial to fiddle with the query in Laravel’s silky-smooth Models. Also you can’t tell from the Unix timestamp integer in PHP what timezone it’s in. Lastly, if ever the app gets big I want most of the processing in PHP not MySQL, as I believe adding more web servers is easier than splitting a social app’s database into shards.

3 – Use datetimes for the database (and the model) and Unix timestamps for the rest of the PHP

That is, you have MySQL datetimes in the database and MySQL datetimes as attributes on your model[3], but translate to Unix timestamps (and the user’s timezone) in the model’s Get function and back in the Set.

Pros: MySQL gets MySQL datetimes so you can see in the database that it’s a date / time and what date / time it is. PHP gets to work with Unix timestamps so it’s happy. You know that the datetime in the database and on the model attribute are in UTC (but you never use them), and you know that the Unix timestamp in PHP is always in the user’s timezone offset.

Cons: You must only ever access the model’s attributes via the Setter and Getter … though some might think that’s a good thing. Also I suspect I won’t be able to set my model attributes as Carbon objects.

Conclusion

In case it’s not obvious, I’m going with option 3, particularly because my perfectionist personality likes the clean separation of UTC on the model and database and user’s timezone on the Unix timestamp in the PHP.

Notes

[1] Not exactly, but more or less, and close enough for my purposes.

[2] Also MySQL has the date format but that doesn’t store time and I need both date and time.

[3] Technically you’re storing a string representation of the MySQL datetime as an attribute on your model and not an actual MySQL datetime – but that’s almost the same thing.

Advertisements
Dates in Laravel/PHP and MySQL

One thought on “Dates in Laravel/PHP and MySQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s