I have an application to publish jobs. Users can select the start and end dates / end times for the time when the job goes live and expires.
Users who publish jobs can be located in any country, but most of them in the UK, so their start and end dates are in their local time. For the time being, however, I have explicitly stated this in order to simplify the application. All times are in the "Europe / London" time zone, but I would like to keep the framework in order to truly internationalize the app at a later date.
Note: I also store the user's time zone in a database column, but as mentioned above, for the time being, all users are located in "Europe / London" when viewing the data.
The question is, will I convert this data to UTC before storing it in the database? Which data type is best for storing these data / times in mysql? DateTime or Timestamp?
I've read a lot of posts about restrictions on using timestamps, such as: For example, the internal conversion of values to UTC and the 2038 issue. Since I convert the dates in my application layer to UTC before saving, it makes sense to use a timestamp field because this conversion would be done twice. No, correct me if I'm wrong or it does not matter. When entered in a time stamp field as UTC, it is output in a time stamp field as UTC.
If I perform a search, such as one of the following queries, to return all the live jobs, I must now convert all the data in that query to the Europe / London time zone.
For example, from:
, TIMESTAMPDIFF(DAY, NOW(), jobs.end_dt) as days_remaining
WHERE jobs.start_dt <= NOW() and jobs.end_dt >= NOW()
, TIMESTAMPDIFF(DAY, CONVERT_TZ(now(),'UTC','Europe/London'), CONVERT_TZ(jobs.end_dt,'UTC','Europe/London')) as days_remaining
WHERE CONVERT_TZ(jobs.start_dt,'UTC','Europe/London') <= CONVERT_TZ(now(),'UTC','Europe/London') AND CONVERT_TZ(jobs.end_dt,'UTC','Europe/London') >= CONVERT_TZ(now(),'UTC','Europe/London')
If you use timestamps as the data type, you need to know which time zone the current time zone is set for MySQL. MySQL converts TIMESTAMP values from the current time zone in UTC to save and back from UTC to the current time zone for retrieval. (This does not occur with other types such as DATETIME.) By default, the current time zone for each connection is the time of the server. Maybe we need to set up our laravel mysql connection configuration as follows to make sure we do not get any wrong results:
// AND in config/database.php
'connections' => (
'mysql' => (
'timezone' => '+00:00', // set to UTC
Do you have any advice and considerations, tips and tricks?
Note: I originally posted this on SO, but no one seems to have any idea … Maybe because this is more of a design issue.