Storing Date/Time as UTC, problem with daylight savings time

https://laracasts.com/discuss/channels/laravel/how-to-convert-timezone-from-users-time-to-utc-on-saving-and-back-from-utc-to-users-time-zone-on-read

This shouldn't be a problem if you store the timezone itself as well in the database. You can use the following code for that in your migration

$table->timestampTz('published_at');    

// Or for the created_at and updated_at
$table->timestampsTz(); 

Documentation: https://laravel.com/docs/5.8/migrations#columns

That doesn't solve for a date/time store in daylight savings time and then retrieved when user is no longer in daylight savings time.

I just store everything as UTC. Users can select their timezone in their profile. Then I just convert to their timezone when displaying.

{{ $model->created_at->timezone(Auth::user()->timezone) }}

Where $user->timezone is a valid php timezone like America/Los_Angeles.

If they are traveling, they just change the timezone in their profile and all dates adjust to new timezone + daylight savings time if observed.

Here's the issue, let's imagine:

On February 15th, a user in timezone CST (Central Standard Time) creates an appointment for 2pm on March 15th 2019-03-15 14:00:00; the event is stored as UTC, which is 2019-03-15 20:00:00 UTC (-06:00).

Then on March 15th, 2019, the user goes to retrieve this event; he is now in CDT (Central Daylight Time -05:00). When the event is retrieved, it applies -05:00 and shows the appointment at 3pm instead of 2pm.

You are assuming the timezone is always constant, but for those who are in timezones that shift daylight savings time, the timezone changes.

creates an appointment for 2pm

Just store a date time for an appointment in user timezone. If a set appointment, no conversion should be needed.

It's not like the user is changing a timezone for an appointment.

The user isn't changing the timezone, it is done automatically in their browser because of daylight savings; so when the appointment is rendered in the browser, it changes it to its timezone.

I was referring to physically changing a time zone. If I have an appointment in Dallas Texas on a certain date at 2 p.m. that's where the appointment will be I won't be flying to London for the appointment.

The physical timezone in Dallas, Texas changes based on daylight savings; so a 2pm appointment stored in CST and then retrieved in CDT will change by an hour.

Time zone has nothing to do with it. An appointment at 2 p.m. on a certain day will not change unless the Law changes when time zones are set again.

If you set an appointment that's 2 p.m. store in UTC and retrieve UTC and convert back to Central Time for display all will be correct.

And if it's not correct that means there's an issue in carbon and you need to do a issue on their GitHub site.

Of course when displaying you have to use the correct date.

Also if you need to store dates as Central Time Zone get them as central time zone, store them as central time zone don't even mess with utc for appointments.

You are assuming the timezone is always constant, but for those who are in timezones that shift daylight savings time, the timezone changes.

No, I wasn't. What I showed would convert to DST or standard time depending on the date (whether DST is currently being observed or not).

If I'm in America/Los_Angeles, and DST isn't currently in effect at the time I make the timezone conversion, it would give me standard time. If it was in effect, it would give me DST time.

Found it where @usman solves the timezone problem:

https://laracasts.com/discuss/channels/general-discussion/user-specified-timezones-config

@ydid you ever find a solution? pretty much all those replies and none actually address the issue.

Look up, the one https://laracasts.com/discuss/channels/general-discussion/user-specified-timezones-config

That @usman and @snapey worked out solves the problem.

I even found another, see the best answer here from @cronix https://laracasts.com/discuss/channels/laravel/displaying-dates-accordingly-to-each-timezone

Php should display the correct time if above is followed, php date time has always worked for me over the last several years.

Edit: use last link.

The point that is being missed here is when you set an appointment in the future, you have to know what the utc time will be in the future not what utc time it is today

Hi @yougotnet,

I've read through this thread and I'm going to attempt to demonstrate with a worked example. (I've had the "pleasure" of working with a system that had to operate in the UK and across Australia and I know how it can cause some confusion to begin with)

Let's take work this through with an example based on France (because the maths is easier!) - France is one hour ahead of UTC normally (UTC+01:00) and **on the 31st March 2019 at 2:00am local time (2019-03-31 01:00:00) the offset changed from +01:00 to +02:00** and the local time jumps from 1:59:59am to 3:00:00am and France observes Daylight Saving Time.

• A calendar event that starts at noon, 28th March (local time) the UTC value will be stored as **2019-03-28 11:00:00** (as we are one hour ahead at that local time).
• A calendar event that starts at noon, 1st April (local time) the UTC value will be stored as **2019-04-01 10:00:00** (as we are two hours ahead at that local time).

Sidenote: In PostgreSQL, the timestamp and timestamptz column types are identical: timestamptz allows time zone functions to be applied, timestamp doesn't!

If we read both of those values out using @Cronix's code:

{{ $model->created_at->timezone('Europe/Paris') }}

...both will present correctly. PHP's datetime functions contains a database of all timezones and offset changes (not just for DST, but for political/government changes as well - sometimes countries switch to match their neighbours).

• PHP sees **2019-03-28 11:00:00** and knows that 1 hour is to be added at that UTC time when you are in France.
• PHP sees **2019-04-01 10:00:00** and knows that 2 hours are to be added at that UTC time when you are in France.

You can in fact check this list via $timezone->getTransitions() in PHP. New PHP versions often contain the changes from IANA database as nations change their rules. https://www.php.net/manual/en/datetimezone.gettransitions.php

Each transition lists the UTC time that the offset gets applied at, what the new offset is, whether that new offset indicates DST from this point until the next change and a localised abbreviation (in the UK that's GMT or BST - Greenwich Mean Time, or British Summer Time)

I don't know exactly what you mean by this comment...

The user isn't changing the timezone, it is done automatically in their browser because of daylight savings; so when the appointment is rendered in the browser, it changes it to its timezone.

...but I will say that any attempt to use Javascript's getTimezoneOffset() with rendering/setting on the client side is best avoided. An offset might be shared by multiple countries at the same time (typically north/south of each other) that don't obey the same transition timestamps and so you cannot conclusively use it to work out their timezone.

KEY POINTS:

• UTC is a single, always incrementing timestamp that carries no offset.
• Timezones define offsets between UTC and local time: at any given UTC time you can apply the offset for that UTC value and timezone to get the local time for the same timezone.
• When you observe those values doesn't matter, what they are does.
• When storing to the database your Carbon instance should carry both timestamp and timezone for the conversion to set UTC correctly in your data store.
• When retrieving, you should apply the appropriate timezone (e.g. User's if you are seeing a log of user activity. Destination airport timezone to see the time when a plane lands locally. Timezone of your present location when showing eta of your Uber ride.)

Hope this helps!