3

Well I know that this question may be asked many times but certainly I've few doubts In my mind, and by the way don't comment that what I've tried, I've tried many ways but am just asking what's the correct and easier way as there are many posts lingering out here with different suggestions for accomplishing these tasks, so I'll explode() my question into smaller questions...

So can you people just guide me where am going right or wrong as am sure many people are confused when it comes to date/time

1) Why/how to save time as UTC in MySQL using PHP? Personally for this I post this using php $year/$month/$day and check the date using checkdate() in mysql date field. so is it ok or should I use timestamp and than explode the retrieved string on front end using php?

2) If I run server from India, should I record default time using date_default_timezone_set("Asia/Calcutta"); and than subtract and add time using php function or while posting only I should check users time zone selected from his user accound and accordingly set a condition kinda:

if(timezoneselected == +5.30) {
echo date_default_timezone_set("Asia/Calcutta");
} elseif(timezoneselected == +anytime) {
echo another country timezone
}

3) last question is how websites like gmail facebook etc manages time? I mean if they are saving datetime according to their server than how they show perfect posted time for each user, even gmail, if I send a mail to another user, my sent time and the person living in another country gets email at his printed local time I mean how we can do this, sorry am not able to explain you perfectly say this example,

facebook: user from India posts, facebook shows posted 8 mins ago, 9mins ago, fine after sometime they show a real date, and that date is perfect according to the time I posted, however if a person from USA updates, say 8 mins ago 9mins ago on his profile but his original posted time is shown correctly to him, and even correctly to me?

sorry for this question but really this will help me understanding this date/time concept and will also be helpful to future users. Thank you!

Random Guy
  • 2,878
  • 5
  • 20
  • 32
  • I personally do d/m/y fields seperately in table so I can make whatever format I want when getting the values to php. It also helps me to do easy order by queries. I only use timestamps when I need exact time to compare to or if I need to do calculations on the time (using sql functions), like selecting something that was 5 hours ago and similar queries. – cen Sep 04 '12 at 04:48

2 Answers2

4

Bottom line, you should store everything UTC

When you display times for a particular user, use a timezone of their choosing. Store the timezone of the user, like "Asia/Calcutta" and simply convert the time when displaying it using the date_default_timezone_set method.

I will attempt to answer your questions from the comment here.

You store everything UTC always. It is the baseline. When you display the times associated with anything you convert based on the user. If you want to display Posted 8 mins ago then you are taking the delta between the current UTC time and the UTC time associated with a post. If you send a message from user A (in India) to user B (in Los Angeles, USA) then you would store the message time in UTC. If user A is viewing it, the time would be converted to "Asia/Calcutta" and if user B is viewing it, the time would be converted to "America/Los_Angeles". Using UTC will make your life a lot easier. Trust me.

sberry
  • 128,281
  • 18
  • 138
  • 165
  • Ok that was I was asking say if I saved time for everything using utc, now you are saying that I should give an option to users to select their time zone and accordingly +/- after retrieving the data from sql right? and will this task also accomplish if I want to show users posted 8 mins ago 9 ins a go and aftr few hours I'll be showing real time? moreover what if a user sends message to another user with different timezone? will php balance the timezone set for both users? – Random Guy Sep 04 '12 at 04:56
  • 1
    If two users message each other, the fact that they're in different timezones doesn't matter. Any stored dates are stored in UTC, so it's a matter of simply setting date_default_timezone_set for each user to their own timezone, and using DateTime objects. Eg: `$date = new DateTime($theDateInUTC); $date->format('d/m/y h:i:s');`. Then whoever is looking at that date will see it relative to themselves. – temporalslide Sep 04 '12 at 05:23
  • thanks bro, ins hort, I use `gmdate()` and add or subtract offset using conditions according to the timezone user has set for him..and btw am using procedural...and I am all clear now, I'll be storing dates and time using `gmdate` in my database, and later set default time zone as per his/her setting or show default timezone to him :) and btw any idea to suggest him/her to set timezone, kinda using his ip to know from where he is login in..?or I should let him select tihe timezone himself? – Random Guy Sep 04 '12 at 05:28
  • 1
    You can use various methods. First you should ask them to set their timezone (since that is most reliable.) Second, you can try using their IP, or you can put something together with javascript like described here: http://stackoverflow.com/questions/4746249/get-user-timezone – sberry Sep 04 '12 at 05:35
  • great, very simple, i'll be storing `gmdate("Y/m/d H:i:s")` in sql and then on the top of the page i'll set default time zone to convert utc to local user time, very simple, thanks bro! – Random Guy Sep 04 '12 at 05:49
1

As described in MySQL Server Time Zone Support:

The current session time zone setting affects display and storage of time values that are zone-sensitive. This includes the values displayed by functions such as NOW() or CURTIME(), and values stored in and retrieved from TIMESTAMP columns. 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.

Therefore, if you use TIMESTAMP type columns, MySQL will handle timezone conversion for you automatically: just set the appropriate timezone for the session in its time_zone variable.

eggyal
  • 122,705
  • 18
  • 212
  • 237