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.
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.
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.
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
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, 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.
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.
 Not exactly, but more or less, and close enough for my purposes.
 Also MySQL has the
date format but that doesn’t store time and I need both date and time.
 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.