timezone issue [SOLVED]

[guardiano78]

Hi,
i have a problem with modified_on field in database.
My timezone is setted to Rome.
For example if i make a modification in virtuemart user profile and save it, in modified_on field in #__virtuemart_userinfos, the time is 2 hour before the real time.
i checked the timezone server for php and mysql and they are ok.

What problem maybe?
thanks

[Jörgen]

The saved time is always the GMT time if I am not mistaken. Then it is adjusted for the actual timezone before it is displayed.
This is so that ther won´t be any double adjustements.

regards

Jörgen @ Kreativ Fotografi

[guardiano78]

thank you for response, what should I do to fix it?

[Jörgen]

There is nothing to fix, why would You fix it ?

regards

Jörgen @ Kreativ Fotografi

[guardiano78]

There are 2 hours between the time written in the database (2 hours less) and the real date time.
I i make a modification at 2016-05-13 10:01:35 in database is written 2016-05-13 08:01:35
I think there is something wrong

[Jörgen]

If You lived in Japan what time would the database be written then ?

It always stores the GMT time and when You use it adjust for the actual time zone.

How can You possible see in which time zone You saved the timestamp ? Believe me this is not an error.

regards

Jörgen @ Kreativ Fotografi

[guardiano78]

ok there are no errors, but for me the problem remains.
I do a specific example:
1. I'm in Italy and i set my joomla timezone to Rome;
2. on my watch is 10.00 am;
3. php and m mysql servers are set at 10.00 am. My provider has just told me that the server has CEST configuration for set times;
4. After login i go to change my address and click save;
5. i check the field modified_on in #__virtuemart_userinfos table and it contain 8.00 am.

This happen also in localhost (server XAMPP).
I can understand that you might not make sense, however, it happens to me and I would like to understand why.
Maybe that override cause this problem?

[guardiano78]

It is not an override problem.
I tried it with a new installation of joomla 3.5.1 and virtuemart 3.0.16.
I just installed the plugin plugin_system_kc__vm_registration_redirect-3.0.0.zip to write in the fields of virtuemart tables.

Same result ... always 2 hours back!

[GJC Web Design]

What Jörgen is saying if I understand him correctly is the time is written to the DB ALWAYS in GMT

the DISPLAY is then adjusted to show your local time if it is displayed.. is this not the case? (if it is displayed somewhere in the admin etc)

[Jörgen]

Thanks GJC

All timestamps should be saved in UTC/GMT time which in Your case is -2 hours. This is stored in the dB. This will be the same time no matter where You are in the world.
Remember that You are looking at the stored data with phpmyadmin and phpmyadmin does not add any time zone correction.

Everywhere You use this time in Your application the offset must be added, not the "raw" timestamp alone which is UTC/GMT time.

If we assume the stored time is 13:00 hours in the database it should be corrected to 15:00 hours when displayed in Italy and in Great Britain it would be displayed to show 13:00 hours. The displayed time will be different depending on where in the world You are. The same moment is displayed as different time in different places on the globe. Saving the timestamp as UTC/GMT makes this possible, saving local Italian time would make it impossible to display the correct time in other time zones. How Do we know that is is saved as italian time, that would take another data base entry. Now we know that it is stored UTC/GMT time and can adjust for the correct timezone.

This is how it works, make a test with a simple Joomla article and compare the stored timestamp for last modification and the displayed time in the Joomla back end. They will differ with 2 hours and that is totally correct.

I hope I made this clear enough.

Jörgen @ Kreativ Fotografi

[guardiano78]

Thank you, you were very clear.
To solve my problem I decided to change my script (which reads the field modified_on from virtuemart tables) in this way:

Code Select Expand


$datatime = new DateTime($modified_on_field);
$datatime->modify('+2 hour');
$new_datatime = $datatime->format('Y-m-d H:i:s');

Thanks for your help.

[GJC Web Design]

Old topic but a better way would be to use time zones  ( then summer time etc is accounted for)

e.g.
set the string to UTC then alter the TZ

Code Select Expand


        $datetime = new DateTime($order_row->created_on, new DateTimeZone('UTC'));
        $datetime->setTimeZone(new DateTimeZone('Europe/London'));
        $order_details->order_date = $datetime->format("Y-m-d H:i:s");

[guardiano78]

Hello GJC Web Design,
thank you :-)