Dynamically setting TimeZone with UTC field

Scriptcase 7 Discussion
,
#1

Hi
I record every transaction in our mySQL tables using TimeStamp UTC
Each user when they log in, is associated with respective TimeZone they are in (example: Europe/Paris, Africa/Johannesburg, America/Central, etc)
As the user logs in they go to a Menu app and in turn can run a wide variety of apps.
I want to be able to set the TimeZone for each user dynamically as they log in, using the TimeZone variable we have on file for each of the users.

I found this piece of code, but can’t seem to make it work.
$timezone = $timezone ? $timezone : DEFAULT_TIMEZONE;
$_SESSION[‘user_timezone’] = $timezone;
date_default_timezone_set($timezone);
db_query(“SET time_zone = ?”, $timezone);

Please can someone tell me what and where to put this into either the Login Application or Menu Application.

#2

hi

just a small note, if you log a time from each user then you will have in the database different times and each one is from different zone!

either you need to store what GMT/UTC was that when you logged it, so you can proceed later to distangish how to deal with them, or you must unify them as one GMT/UTC yet the users will deal with times according to their location.

check in macros, there are some related stuff to time-zone

#3

http://php.net/manual/en/class.datetimezone.php
http://php.net/manual/en/function.date-default-timezone-set.php

As MikeDE said. I would personally keep the system time as GMT, you can set that in your php.ini. Due to possible summertime/wintertime problems this is always the easiest way.
On the other hand you can not always find out in what timezone the user is in. Some solutions are here: http://stackoverflow.com/questions/6939685/get-client-time-zone-from-browser

#4

MikeDE - You are absolutely correct. I do want to store it as GMT/UTC and then when the user pulls up their respective data it converts it to their respective TimeZone.

I am storing as per GMT/UTC, its the converting that I am battling with. I have the right format for the TimeZones (Europe/Paris, Africa/Johannesburg, etc).
I tried using various combinations in the SQL Query but constantly displayed as blank
The way the table is setup is TimeStamp

Can you help?

#5

Larry,

There are actually 3 different time settings: Server Time Zone, MySQL Time Zone, and PHP Time Zone.

Make sure your SQL Time zone is either set to SYSTEM (then it will follow the server’s time zone) or to GMT (google “MySQL how to set time zone”)

Set your PHP time zone as your user wants to see it (I set it in the login app. google “PHP how to set time zone”). You can set a time zone that applies to the current session only.

and finally, use Unix Timestamps, not textual “Date” or “Time” fields, otherwise you will have to convert time zones every time you read or write from the database.

If you really must use textual fields, then set the MySQL variable @@session.time_zone variable to match the PHP time zone and everything should work seamlessly.

Dave

#6

[QUOTE=Larryh1115;33342]MikeDE - You are absolutely correct. I do want to store it as GMT/UTC and then when the user pulls up their respective data it converts it to their respective TimeZone.

I am storing as per GMT/UTC, its the converting that I am battling with. I have the right format for the TimeZones (Europe/Paris, Africa/Johannesburg, etc).
I tried using various combinations in the SQL Query but constantly displayed as blank
The way the table is setup is TimeStamp

Can you help?[/QUOTE]

hi Larry

Here rr and Dave explained it all thankfully, even better than than I have in mind :slight_smile:

#7

Hi Dave,

I am having a similar issue to what has been discussed here. I am storing time using TIMESTAMP and want to retrieve in local time for GUI display.
I am able to do this by placing the following statement in SQL Preparation
SET time_zone = “America/New_York”

My question is, how can I do this dynamically using a user selected timezone?
Ideally, I would do something like this

SET time_zone = ‘[glo_TIMEZONE]’; where [glo_TIMEZONE] is a global variable containing text like ‘America/New_York’ chosen by the user in another application.

Thanks in advance for any advice

Jim

#8

As far as I know you need this: http://php.net/manual/en/datetime.settimezone.php

This sets the datetime for a particular datetime object. So this would be then client specific as it belongs to a php session. I havent used it myself since we only use one timezone.