...

View Full Version : Timestamp vs DateTime



bacterozoid
01-22-2012, 12:15 PM
I know that time() in PHP will give me a GMT timestamp. I know that when I use date() it converts that to whatever the current timezone is.

I have a basic timeclock application with timeclock entries and timesheets. It works by pressing a button to clock in and out. Right now I'm storing everything as a timestamp in the database, but I'm not sure that's the right way to do it. I feel like changing time zones would break it if I do that.

Database overview for visual people:

timeclock_entries
timeclock_entry_id (int)
clock_in (what datatype?)
clock_out (what datatype?)

timesheets
timesheet_id (int)
timesheet_start (what datatype?)
timesheet_end (what datatype?)

Thoughts?

Fou-Lu
01-22-2012, 04:19 PM
It doesn't matter which you use. In PHP world timezone is applied at the level of a locale aware function, not the data. So a timestamp in UTC will be identical to a timestamp in America/Chicago when generated at the same time. All you need to do to change a timezone is specify it using date_default_timezone_set and providing it with the timezone you are in.

My recommendation is that for a new project to use a DateTime datatype which in PHP can use the DateTime class or strtotime to convert it. If its a modified project currently using an integer, I would leave it as such. The only real major flaw of an integer timestamp is in another 26 years you will run out of time available (the real year 2000 if you will). MySQL (or any database really) can convert its datetime type to a unix timestamp, and vice versa.

Faimos
01-22-2012, 10:25 PM
This allows you to use either, but requires an "x unit(s) ago" format

http://css-tricks.com/snippets/php/time-ago-function/

tangoforce
01-22-2012, 10:43 PM
The only real major flaw of an integer timestamp is in another 26 years you will run out of time available (the real year 2000 if you will).

I read somewhere that 64bit processors had extended that lifetime?

Fou-Lu
01-22-2012, 11:38 PM
I read somewhere that 64bit processors had extended that lifetime?

They have, but PHP is still 32 bit.

tangoforce
01-23-2012, 02:25 PM
Whoops!

bacterozoid
01-23-2012, 03:03 PM
Thanks. I think I'm going to go ahead and switch everything over to datetime in this situation, if only to avoid this:

Event happens at 4:00 pm eastern
--User changes time zone to central--
Now event displays as happening at 3:00pm central

While I know that the time didn't actually change since it's stored as UTC, the way it gets displayed does, and I don't want to have to display the time zone on everything.

tangoforce
01-23-2012, 03:10 PM
Timestamps work better in most situations I've found. Not only does it allow you to order the results you pull from a database a lot easier but you can also use them for any timezone with ease (as mentioned by Fou above).

Using anything else to me seems pointless - especially for an international site where you may need to work with different timezones as php makes this easy if you use timestamps.

Fou has said that you can use DateTime with strtotime() to get a timestamp where needed but again this means that you're still using a timestamp after all the effort (and you're still limited to the 32bit issue mentioned above). I expect php will eventually become 64bit as its rare to see non 64bit computers out there these days (ok there are netbooks but I suspect those will got 64b eventually too).

Fou-Lu
01-23-2012, 03:24 PM
Oh yeah, by 2038 x64 would be obsolete as well. Who knows what the world of computing will be like by then!
I don't mean that DateTime and strtotime are comparable, I mean you can use either when using a DateTime datatype in the database. The DateTime class in PHP isn't a direct timestamp as it appears the underlying is all the components of the time (year, month, day etc), while strtotime is a simple integer result.

Dealing with timezones is very easy though regardless of whether you choose integers or DateTime datatypes. No matter what the choice, PHP does need to interact with the date you have provided in order to display it as you want - storing as an int requires the date() call, while storing as a DateTime would require date() with strtotime or DateTime and format, otherwise it is a simple string and will not adjust its display based on timezone.
Fortunately a lot of work has been put into datetime type's in 5.x+, and each version seems to have something new.

tangoforce
01-23-2012, 03:28 PM
Oh yeah, by 2038 x64 would be obsolete as well. Who knows what the world of computing will be like by then!

Makes you wonder doesn't it. Presumably by then we'll be on at least 128bit processors. I say we just skip that and go to the 1MBit processors and have it done with. At least that way we won't have to keep up with the upgrade race! :D

bacterozoid
01-25-2012, 12:40 AM
Sorry to bump this back up, but I have a follow-up question.

As an example, here's what's happening:


PHP time zone is eastern
Event happens at 5:30 pm
Change PHP time zone to central
Event displays as happening at 4:30 pm


I want this to happen:


PHP time zone is eastern
Event happens at 5:30 pm
Change PHP time zone to central
Event displays as happening at 5:30 pm


I converted my int(10) timestamp columns to datetime columns to start. When I put time in, I call from_unixtime(). When I pull time out, I call unix_timestamp(). While this would be desirable in a lot of situations, I think I need to leave the conversion on the select out. It would look like this:

IN: timestamp > from_unixtime() > mysql datetime
OUT: mysql datetime

If I want to get a timestamp in PHP, I can use strtotime().

Does this sound about right? I have the hardest time with time formats, so I appreciate the help. I know this will all click one day.

tangoforce
01-25-2012, 12:49 AM
The reason your times change is because you change timezone. PHP will recalculate the difference automatically.

That means it will show the time relative to each zone. If something happened at 5:30 in the eastern zone then it happened at 4:30 in the central zone and so PHP is correct (thats of course assuming there is a 1hr difference - I'm not american so don't know your zones).

The only way to avoid this is by not changing zones part way through the process.

bacterozoid
01-25-2012, 12:51 AM
Well, that's the thing. I want to be able to allow the time zone to change. If an event happened at 5:30 one day, then no matter what time zone changes to in the future, I want it to always say it happened at 5:30.

In the case of my application, this is the most sensible way to display the data.

Edit: As I mentioned, if I don't use unix_timestamp() on my mysql select statement, I can simply use strtotime() in PHP to get a timestamp with the value I want. I'm just wondering if this is the right way to accomplish what I'm trying to do.

tangoforce
01-25-2012, 01:14 AM
OK, I'm seeing two completely conflicting issues with what you've said:


Well, that's the thing. I want to be able to allow the time zone to change.

You want to allow the time zone change..



If an event happened at 5:30 one day, then no matter what time zone changes to in the future, I want it to always say it happened at 5:30.

But you don't want a timezone change. You want it to continue to display the same time even on the other side of the world.

To be honest, I don't actually think you know what it is you do want. You want users to be able to change timezones but you don't want changing timezones to have any effect at all - which is the very purpose of using timezones in the first place :confused:

Forget it. Just create a manual list of timezones, store them in the DB, allow each user to pick it and just whack the name of it onto the end of your 'non-time-zoned' time. That way you have exactly what you want, non functioning timezone changes with the zone showing as being the users.

Job done :thumbsup: :cool:

EDIT:
At the top of my post it says I posted this at 12:14AM. In your timezone it will say something in your local timezone. That means that both you and I can figure out when I made the post, how long between each reply etc. THAT is how timezones are supposed to work. What would be the point of you choosing your timezone, me choosing mine and I see your timezone when you post? - It would be very confusing.

bacterozoid
01-25-2012, 02:02 AM
This is for a time clock application. Let's say I clock in at 8:00 every day. If I change time zones, I don't want all of my previous days to suddenly say I clocked in at 7:00. While the sum of the hours is still the same, I don't want the time I came in to be misrepresented.

Nobody other than the person who created the entry will ever see the times, so confusion with other people in other time zones won't happen.

tangoforce
01-25-2012, 02:07 AM
One option you might want to consider then is to switch between the zones showing both the 'activity' timezone and the users timezone.

That way its win-win :)

Fou-Lu
01-25-2012, 02:43 PM
If you want to deal with actual dates, this is a prime example for using DateTime and DateTimezone. Timezone would need to be tracked for each location of 'clock in', and you can use datetimezone to calculate the offset. So if I'm in utc and want to check the results of someone who clocked in in Chicago:


<?php

date_default_timezone_set('UTC');
$time = new DateTime();
$dt = new DateTime('yesterday 09:00', new DateTimeZone('America/Chicago'));

printf("Current time: %s" . PHP_EOL, $time->format('F j Y h:i:s (e)'));
printf("Clocked in at %s" . PHP_EOL, $dt->format('F j Y h:i:s (e)'));

?>

$dt will contain the offsets to America / Chicago regardless of where you are listing the time.

tangoforce
01-25-2012, 03:05 PM
If you want to deal with actual dates, this is a prime example for using DateTime and DateTimezone. Timezone would need to be tracked for each location of 'clock in', and you can use datetimezone to calculate the offset. So if I'm in utc and want to check the results of someone who clocked in in Chicago:


<?php

date_default_timezone_set('UTC');
$time = new DateTime();
$dt = new DateTime('yesterday 09:00', new DateTimeZone('America/Chicago'));

printf("Current time: %s" . PHP_EOL, $time->format('F j Y h:i:s (e)'));
printf("Clocked in at %s" . PHP_EOL, $dt->format('F j Y h:i:s (e)'));

?>

$dt will contain the offsets to America / Chicago regardless of where you are listing the time.

Clever!

Fou-Lu
01-25-2012, 03:52 PM
The new datetime stuff in PHP is so wicked awesome. Too bad its still volatile, so until PHP 6 I'd suspect a part of any answer to datetime will be "what version of PHP is in use?".



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum