timestamp vs timezones

[Destramic]

hey guys I wanting some information on the best way to store a timestamp and how I would convert that timestamp to another timezone please?

 

im guessing I would firstly store all timestamps to the database in GMT OR BST then convert after according to users timezone preference?

 

Is there a good website for this please on how this is possible...thank you

[gizmola]

Make sure all your servers are setup to use GMT. When storing a timestatmp or datetime in a mysql database, I wrote about this topic extensively here: http://www.gizmola.com/blog/archives/93-Too-much-information-about-the-MySQL-TIMESTAMP.html

 

As long as you don't need to store future or distant past date time values beyond the range of the timestamp, then timestamp is the most efficient type. I cover all the ins and outs of what timestamps do for you automagically in mysql, and how to enable or disable that behavior. For example, by default the first timestamp in a mysql table will be updated when that row is inserted or updated.

 

Timezone is a user attribute or preference. You should store a timezone preference in the member/user/profile row. From that point, you can easily read the original GMT timestamp value into a PHP Datetime object, and apply their timezone to it, and then display the value to them in their timezone. This is all covered in the documentation for the php datetime object.

 

To generate the list of timezones:

 

$tzlist = DateTimeZone::listIdentifiers(DateTimeZone::ALL);

[Destramic]

brilliant article...definitely gonna have a update timestamp ...thank you

 

well I was thinking of using

<?php
define('TIMEZONE', 'Europe/London');
date_default_timezone_set(TIMEZONE);
?>

to set the default timezone so when I used now() in my queries it'll be saved as gmt?

 

and yes like you said I have a column for the user to select his/her timezone but how do I change a timestamp to another timezone?

 

thanks

[kicken]

to set the default timezone so when I used now() in my queries it'll be saved as gmt?

PHP's timezone setting has no effect on mysql's date/time functions. You'd need to set the OS's timezone or set it on your connection by issuing a SET time_zone = timezone query.

 

I prefer to make sure I store all times in the UTC timezone and then you can use the UTC_TIMESTAMP() function in mysql without having to worry about what it's timezone is configured.

[Destramic]

Looking at the manual and as you say using Utc_timezone() to store the date does seem a easier way to do things...how would I select the timestamp then and convert it to another timezone on select?...thanks for your help guys

[gizmola]

Looking at the manual and as you say using Utc_timezone() to store the date does seem a easier way to do things...how would I select the timestamp then and convert it to another timezone on select?...thanks for your help guys

I already covered that -- you use the Datetime and the stored user timezone string. What that function would allow is for the storage of a UTC/GMT timezone regardless of the server configuration, however, if the server is already GMT which is best practice anyways, that's not needed.

[Destramic]

Ok let me clarify a few things please (sorry)...so when inserting data I set time zone in the query to Europe/London...then when selecting I set the timezone to the user's timezone preference so that when I return the timezone column from my row it would convert to the user's preference? :/

[gizmola]

Ok let me clarify a few things please (sorry)...so when inserting data I set time zone in the query to Europe/London...then when selecting I set the timezone to the user's timezone preference so that when I return the timezone column from my row it would convert to the user's preference? :/

No. You always insert a UTC/GMT oriented value. If the server timezone is already UTC then NOW() will set it to the current datetime. Also using the timestamp default properties are going to do the equivalent of setting it to NOW().

 

You don't want to try and store a value relative to the the user's timezone. Thus you know that all the time values you have stored are always UTC. You only apply the user's timezone at the point you need to display it TO THEM.

 

However, IF the server has not been configured to use UTC, and you can not control that, NOW() will not be returning a UTC timestamp. In that case, you still have the option of substituting UTC_TIMESTAMP() in place of NOW() or the default timestamp behavior.

 

You can easily experiment with these using phpmyadmin or the mysql command line client and run some simple queries against a test table to gain confidence.

 

No matter what --- your goal is to always insert a UTC timestamp into the database. You have to understand that the timezone is not intrinsic to the timestamp. There is nothing about a timestamp value that indicates the relative timezone. It is just a number. You as the developer have to control the baseline of insuring that those values when inserted, are always relative to UTC.

 

This is why a specific user's timezone should be considered a preference. As such, this is why I explained that you should store their timezone string in their user row as a string. The method i provided returns the full list suitable for a drop down list box, and you can of course default it to UTC. Write a simple script that displays that list of strings and it should be easy to see how this works.

 

You can then provide them a way in their profile to change the default. In a recent implementation I utilized an IP based geolocation service to set the default timezone to set their default to something sensible.

 

Whenever you do queries that present them these values, you simply need to use the value you select as the basis for a php DateTime object.

 

That object intrinsically supports the use of timezone objects, so that you can take your UTC DateTime and transform it to the user's Timezone for display in your application.

 

Assume that you loaded a row into an array variable named $row. You have a timestamp column named 'created' that was set when the user's account was first inserted. Their timezone string is stored as a varchar named 'tz'.

 

 

$createdDT = new DateTime( $row['created'], new DateTimeZone("UTC"));
$createdDT->setTimezone( $row['tz'] );
echo $createdDT->format('Y-m-d H:i:s');

The display will reflect the user's timezone.

 

Depending on what you want to do there are some nice libraries built on top of the DateTime object to do things like display time differential between DateTime objects as strings and do simply addition/substraction using calendar concepts like days, weeks and months.

 

One that I know of that has been out for a while and completely wraps the DateTime is Carbon.

 

It's important to start with the php DateTime object in your server side code, as it's the basis for lots of flexible functionality.

Hope this clarifies everything for you.

[Psycho]

@Gizmola

 

You obviously know this better than I and you've provided some good information in this thread and the linked page. But I question one thing that you've stated multiple times. For example:

 

 

However, IF the server has not been configured to use UTC, and you can not control that, NOW() will not be returning a UTC timestamp. In that case, you still have the option of substituting UTC_TIMESTAMP() in place of NOW() or the default timestamp behavior.

 

There is no need to normalize the DB to be GT time. MySQL will always store a timestamp relative to GMT. http://dev.mysql.com/doc/refman/5.5/en/datetime.html

 

 

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval

 

As you stated, timestamps are agnostic with respect to timezones. The only thing that changing the timezone of the server affects is how the values are retrieved from the database. If you retrieve the value as a date string (which is the default format: YYYY-MM-DD HH:MM:SS) it will be formatted based upon the timezone of the server. However, since you most likely want a numeric timestamp anyway so it can be formatted in PHP, it would be easiest to use UNIX_TIMESTAMP(fieldname) when selecting the data.

 

To reiterate, there is no need to force the value to be GMT when saving a timestamp, but it should be specifically retrieved as a GMT value when used. Doing this it doesn't matter what timezone the server is set for.

[Destramic]

I apologies for my slowness in digesting this information haha...well I've come to theory that when all data is added to the database then I will use the UC_TIMEZONE() function

 

when selecting the information I could use ?

CONVERT_TZ(created, UC_TIMEZONE(), America/Akta)

please tell me I've got it haha :/

[gizmola]

@Psycho:

 

 

Well yes and no. It does store timestamps as UTC, but that is irrelevant if the server's timezone isn't UTC, since it will convert that internal value on the fly to whatever the timezone of the server is.

[Psycho]

Well yes and no. It does store timestamps as UTC, but that is irrelevant if the server's timezone isn't UTC, since it will convert that internal value on the fly to whatever the timezone of the server is.

 

Right, that is why the query to retrieve the value should specify for the value to be returned in UTC. That way there is no dependency on the server being set for any specific timezone. It would work correctly even if the application has to be moved to another server with a different timezone setting.

[Destramic]

But using utc_timestamp would be good practice incase server isn't British as phyco said...thank you for your help guys...much appreciated