CODEWITHSUNDEEP
mysqllaravellaravel-5php-carbon
wmfairuz
wmfairuz

Reputation: 1043

how timestamp's timezone works in a database and/or Laravel

I have a question regarding timezone for timestamps.

How the timezone in app.php is used? I realized that the behavior is different if I create a timestamp using Carbon or if I query the value (timestamp) from the DB.

Note: My MySQL use system's timezone which is GMT+8 or Asia/Kuala_Lumpur.

  1. Example #1
    • Set timezone="UTC" in app.php
    • Create a Carbon instance 
>>> new Carbon\Carbon;
=> Carbon\Carbon @1572404830 {#3496
     date: 2019-10-30 03:07:10.625282 UTC (+00:00),
   }
>>>
  1. Example #2
    • Set timezone="Asia/Kuala_Lumpur" in app.php
    • Create a Carbon instance 
>>> new Carbon\Carbon;
=> Carbon\Carbon @1572404816 {#3520
     date: 2019-10-30 11:06:56.316851 Asia/Kuala_Lumpur (+08:00),
   }

For example #1 and #2, this is for me, expected. You got different value based on the timezone. Things got a little weirder (at least for me), when we query a timestamp from the DB.

  1. Example #3
    • Set timezone="UTC" in app.php
    • Query from DB
>>> RefCyberCity::whereDataSource('ccms')->take(1)->first()->updated_at
=> Illuminate\Support\Carbon @1572083605 {#3531
     date: 2019-10-26 09:53:25.0 UTC (+00:00),
   }
  1. Example #4
    • Set timezone="Asia/Kuala_Lumpur" in app.php
    • Query from DB 
>>> RefCyberCity::whereDataSource('ccms')->take(1)->first()->updated_at
[!] Aliasing 'RefCyberCity' to 'App\RefCyberCity' for this Tinker session.
=> Illuminate\Support\Carbon @1572054805 {#3491
     date: 2019-10-26 09:53:25.0 Asia/Kuala_Lumpur (+08:00),
   }

We can see that both output 2019-10-26 09:53:25.0 but the timezone is different.

Upvotes: 2

Views: 4739

Answers (1)

Zoe Edwards
Zoe Edwards

Reputation: 13667

Dates coming from the database are effectively a string, and although MySQL has its own timezone information, that is not reflected in a date string without a timezone in it.

The Laravel DB drivers are asuming that it was written as the timezone that’s set in the app config. If you were to change your timezone mid-project, this would throw out all your dates.

My personal advice on timezones is to keep everything as UTC on your application and servers, then only change the timezone at the last possible moment (such as in the view), based on the user’s preference. This also allows you to output the UTC timestamp and have JavaScript update it to the user’s locale. Timezones are very hard, and if your country has daylight savings, timezones become even more confusing as Europe/London for example shifts timezone depending on the year.

Upvotes: 5

Related Questions