Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Regular Coder bacterozoid's Avatar
    Join Date
    Jun 2002
    Location
    USA
    Posts
    490
    Thanks
    24
    Thanked 35 Times in 35 Posts

    Timestamp vs DateTime

    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?

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    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.

  • Users who have thanked Fou-Lu for this post:

    bacterozoid (01-23-2012)

  • #3
    New Coder
    Join Date
    Jan 2012
    Location
    Seattle, WA
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This allows you to use either, but requires an "x unit(s) ago" format

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

  • #4
    Senior Coder
    Join Date
    Feb 2011
    Location
    Your Monitor
    Posts
    4,281
    Thanks
    57
    Thanked 523 Times in 510 Posts
    Blog Entries
    5
    Quote Originally Posted by Fou-Lu View Post
    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?
    See my new CodingForums Blog: http://www.codingforums.com/blogs/tangoforce/

    Many useful explanations and tips including: Cannot modify headers - already sent, The IE if (isset($_POST['submit'])) bug explained, unexpected T_CONSTANT_ENCAPSED_STRING, debugging tips and much more!

  • #5
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Quote Originally Posted by tangoforce View Post
    I read somewhere that 64bit processors had extended that lifetime?
    They have, but PHP is still 32 bit.

  • #6
    Senior Coder
    Join Date
    Feb 2011
    Location
    Your Monitor
    Posts
    4,281
    Thanks
    57
    Thanked 523 Times in 510 Posts
    Blog Entries
    5
    Whoops!
    See my new CodingForums Blog: http://www.codingforums.com/blogs/tangoforce/

    Many useful explanations and tips including: Cannot modify headers - already sent, The IE if (isset($_POST['submit'])) bug explained, unexpected T_CONSTANT_ENCAPSED_STRING, debugging tips and much more!

  • #7
    Regular Coder bacterozoid's Avatar
    Join Date
    Jun 2002
    Location
    USA
    Posts
    490
    Thanks
    24
    Thanked 35 Times in 35 Posts
    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.

  • #8
    Senior Coder
    Join Date
    Feb 2011
    Location
    Your Monitor
    Posts
    4,281
    Thanks
    57
    Thanked 523 Times in 510 Posts
    Blog Entries
    5
    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).
    Last edited by tangoforce; 01-23-2012 at 02:14 PM.
    See my new CodingForums Blog: http://www.codingforums.com/blogs/tangoforce/

    Many useful explanations and tips including: Cannot modify headers - already sent, The IE if (isset($_POST['submit'])) bug explained, unexpected T_CONSTANT_ENCAPSED_STRING, debugging tips and much more!

  • #9
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    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.

  • #10
    Senior Coder
    Join Date
    Feb 2011
    Location
    Your Monitor
    Posts
    4,281
    Thanks
    57
    Thanked 523 Times in 510 Posts
    Blog Entries
    5
    Quote Originally Posted by Fou-Lu View Post
    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!
    See my new CodingForums Blog: http://www.codingforums.com/blogs/tangoforce/

    Many useful explanations and tips including: Cannot modify headers - already sent, The IE if (isset($_POST['submit'])) bug explained, unexpected T_CONSTANT_ENCAPSED_STRING, debugging tips and much more!

  • #11
    Regular Coder bacterozoid's Avatar
    Join Date
    Jun 2002
    Location
    USA
    Posts
    490
    Thanks
    24
    Thanked 35 Times in 35 Posts
    Sorry to bump this back up, but I have a follow-up question.

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

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


    I want this to happen:

    1. PHP time zone is eastern
    2. Event happens at 5:30 pm
    3. Change PHP time zone to central
    4. 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.

  • #12
    Senior Coder
    Join Date
    Feb 2011
    Location
    Your Monitor
    Posts
    4,281
    Thanks
    57
    Thanked 523 Times in 510 Posts
    Blog Entries
    5
    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.
    See my new CodingForums Blog: http://www.codingforums.com/blogs/tangoforce/

    Many useful explanations and tips including: Cannot modify headers - already sent, The IE if (isset($_POST['submit'])) bug explained, unexpected T_CONSTANT_ENCAPSED_STRING, debugging tips and much more!

  • #13
    Regular Coder bacterozoid's Avatar
    Join Date
    Jun 2002
    Location
    USA
    Posts
    490
    Thanks
    24
    Thanked 35 Times in 35 Posts
    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.

  • #14
    Senior Coder
    Join Date
    Feb 2011
    Location
    Your Monitor
    Posts
    4,281
    Thanks
    57
    Thanked 523 Times in 510 Posts
    Blog Entries
    5
    OK, I'm seeing two completely conflicting issues with what you've said:

    Quote Originally Posted by bacterozoid View Post
    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..

    Quote Originally Posted by bacterozoid View Post
    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

    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

    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.
    Last edited by tangoforce; 01-25-2012 at 12:16 AM.
    See my new CodingForums Blog: http://www.codingforums.com/blogs/tangoforce/

    Many useful explanations and tips including: Cannot modify headers - already sent, The IE if (isset($_POST['submit'])) bug explained, unexpected T_CONSTANT_ENCAPSED_STRING, debugging tips and much more!

  • #15
    Regular Coder bacterozoid's Avatar
    Join Date
    Jun 2002
    Location
    USA
    Posts
    490
    Thanks
    24
    Thanked 35 Times in 35 Posts
    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.


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •