I'm not sure you need to do this. The value that is actually stored in the MySql DB is still a large integer with the seconds since the Unix Epoch. Its given the mysql TIMESTAMP datatype but this is just an integer of the required length

For convenience and to make it readable by humans MySql converts it to the string format automatically when you or Laravel runs a select query but if you want to see the actual integer value you can use the unix timestamp function

For example .... try that on any of your tables with timestamps created by laravel

SELECT created_at , unix_timestamp(created_at) FROM users;

You can also insert it as a unix timestamp and work the other way For example you might have some other program that updates the timestamps value that lavavel might read.

UPDATE users SET updated_at = FROM_UNIXTIME('1398388932')

abelgo said:

I'm not sure you need to do this.

Hi, thank you for your reply. :-)

Yes, but to make Laravel do that magically would still require you to create some override methods because it doesn't do UNIX_TS or FROM_UT internally. So isn't that just as much work as overrides for INTs?

I don't want to have to explicitly set my created_at and updated_at columns on an update or add a FROM_UT(created_at) on selects no matter which solution I use. So in order to just do:

$col->email = '[email protected]';
$col->save();

or

$ts = $user->find(1)->created_at;

You have to use overrides with my method or the more common one you mention. Isn't that correct?

In your example:

For convenience and to make it readable by humans MySql converts it to the string format automatically when you or Laravel runs a select".

Yes, but it is converted by MySQL based on very dubious timezone and locale logic. From the MySQL manual:

"Values for TIMESTAMP columns are converted from the current time zone to UTC for storage, and from UTC to the current time zone for retrieval."

And

"Note: If you use UNIX_TIMESTAMP() and FROM_UNIXTIME() to convert between TIMESTAMP values and Unix timestamp values, the conversion is lossy because the mapping is not one-to-one in both directions. For example, due to conventions for local time zone changes, it is possible for two UNIX_TIMESTAMP() to map two TIMESTAMP values to the same Unix timestamp value."

I don't know, it just seems like a lot of extra headache to get reliable date/time stuff using MySQL native formats. Plus using INTs is less DB vendor specific and more portable. Of course it's just personal preference and I'm not bashing, just really want to have a reliable solid solution that is TZ agnostic.

However, just as a note, if someone reads this and wants to set the MySQL session timezone to match Laravel's "UTC", you can add an option to the app/config/database.php file: http://forumsarchive.laravel.io/viewtopic.php?id=12132 and then your dates will always be UTC. (If you use this method, remember to set time_zone = '+00:00' on mysql command line or homebrew service if working outside of Laravel or in Sequel Pro).

BTW: Do you know of any way to make the overrides I've set above more global so you don't have to use extend to pull them in to other classes?

jhauraw said: I don't know, it just seems like a lot of extra headache to get reliable date/time stuff using MySQL native formats.

DATETIME doesn't do anything with timezones, only TIMESTAMP does. DATETIME will always store exactly what you give it.

I think I'm a bit confused about what exactly you want to achieve. As its seems to be what you want is already there. I might be not be understanding something. But this is how I see it.

In the database your timestamps are stored as ints in seconds since the Epoc. Basically a counter of the number of seconds since int UTC or GMT since some time back in the 1970s, This includes the created_at and updated_at.

UTC is just the time at that place out of london in Greenwhich where they have good clocks but expressed with an ofset in where you are now. So in the database its basically the time in london. However because I happen to be on the other side of the earth +12 hours ahead of London we say the time here in UTC is GMT +12 hours

That means when I do Select created_at from users on my database it spits out the time with 12 hours already added to it.

this is where it starts to get tricky. In your config.app.php you set a timezone.

To make things work for me I have to tell laravel that i am way over here in New Zealand 12 hours in the future so I set the time zone to

'timezone' => 'Pacific/Auckland',

that means when I do

$users->name = 'future man'; $user->save();

laravel already dose the conversion using the local time it gets from my server an goes hay what time is it compared to GMT in London then it gets saved nicely in my DB as the correct number of seconds since 1970 somthing.

that number that is saved is identical to the number that would have been saved if some one in London with there time zone set correctly on there server had done exactly the same thing in there DB

that means those two numbers are comparable no matter what time was written on my watch in local time if the events happened at the same time on earth they get the same time.

What might go wrong is if some one sets the time zone wrong or tries to compare times that are already been represented in the local time. Or worse the clocks on my server were not in sync with the clocks on server the guy in London was using. The chances are they wont be in perfectly in sync just close.

So just like you pointed out with the problems or catches you have to be aware of when using UNIX_TIMESTAMP() and FROM_UNIXTIME() if you start calling those functions with different clocks on different machines that may not be in sync you database clock probably has a very slight difference between the web server you so you can run into problems when what comes back as a unix time stamp is not always the same is if you get it from a different source.

The trick then is just to stick with one source.

Daniel15 said:

jhauraw said: I don't know, it just seems like a lot of extra headache to get reliable date/time stuff using MySQL native formats.

DATETIME doesn't do anything with timezones, only TIMESTAMP does. DATETIME will always store exactly what you give it.

Yes of course. I was just using date/time proverbially not referencing the MySQL DATETIME type. I just meant in general the issues with MySQL giving opinions on what it returns with TIMESTAMP is something I don't like and many others don't either. The links on the Githup repo I made show very compelling reasons to use INTs.

abelgo said: What might go wrong is if some one sets the time zone wrong or tries to compare times that are already been represented in the local time. Or worse the clocks on my server were not in sync with the clocks on server the guy in London was using. The chances are they wont be in perfectly in sync just close.

That's why I prefer to use INTEGER column type instead of TIMESTAMP to store the Unix Epoch => so as to avoid exactly what you mention in "What might go wrong".

I'm not a fan of overriding and try and use something the way it was made if possible. What I will probably settle on is keeping Laravel app/config/app.php => 'timezone' => 'UTC', and then using the PDO option in app/config/database.php to set the MySQL @@session.time_zone = '+00:00';

I've also set my local dev enviro MySQL vagrant 'my.cnf' with '+00:00'. Now SequelPro, production server and localdev enviro all use UTC and so MySQL will return the timestamp without any local offset applied.

I'd prefer to use INTs per my original post, haven't figured out a good way to globally override Laravel and not have to extend or use full namespace paths.

Thanks for all the tips! I'm more happy to have found Laravel than anything else.

I'm not a fan of overriding and try and use something the way it was made if possible. What I will probably settle on is keeping Laravel app/config/app.php => 'timezone' => 'UTC', and then using the PDO option in app/config/database.php to set the MySQL @@session.time_zone = '+00:00';

So over the last couple of weeks I have reached a similar conclusion. What I eventually did is this.

• Keep all times in UTC and set that as the default in the app config.
• Keep the users local time zone setting in there account profile.
• format all times in the local time when they are displayed in a view.
• When returning external programs set a date in the DB they always set it with the UTC_TIMESTAMP()
• When setting a date I always use $update_time = new DateTime(); to return UTC.

In my situation I have database and users in different countries UTC was the only real solution. I still use the created_at and update at fields however there are times when I must override the values laravel places

When I need to do this I simply call the DB directly like so

$sql = 'UPDATE critical_timestamps_table  SET updated_at = FROM_UNIXTIME(' . $thisfreekingtime .') WHERE id = ' . strval($this->id);
DB::statement($sql);

No problems with laravel messing with the time.