SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Enthusiast
    Join Date
    Sep 2008
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Losing an hour formatting a MySQL time

    Very confusing thing for me. I am retrieving a time from MySQL and then converting it from 19:00:00 to 7:00 PM using this:

    $start_time_db = $row['start_time'];
    $start_time = date("g:i A", $start_time_db);

    I have confirmed that the value of $start_time_db is actually 19:00:00.

    But when I display $start_time to my site it loses an hour and comes out as 6:00 PM instead of 7:00 PM. Any explanation on why and how to fix this?

  2. #2
    SitePoint Evangelist ColinHughes's Avatar
    Join Date
    Sep 2004
    Location
    Spain
    Posts
    473
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $start_time_db strtotime($row['start_time']);
    $start_time date("g:i A"$start_time_db); 

  3. #3
    SitePoint Guru bronze trophy
    Join Date
    Feb 2013
    Posts
    744
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    btw, You can add or subtract minutes, hours, days, months like this if need be.
    PHP Code:
    $start_time_db $row['start_time'];
    $start_time date('g:i A'strtotime($start_time_db " + 1 hour")); 

  4. #4
    SitePoint Enthusiast
    Join Date
    Sep 2008
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Gentlemen for your assist. That of course works. This has to be the best place on the internet for quick and accurate responses. You guys are the best. Thanks again!

  5. #5
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,837
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Is there a particular reason why you are not extracting the time directly from the database in the format you want and are extracting in one format and then converting to another?
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  6. #6
    SitePoint Enthusiast Belsnickle's Avatar
    Join Date
    Feb 2012
    Location
    Chico, CA
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by RGStephens View Post
    Very confusing thing for me. I am retrieving a time from MySQL and then converting it from 19:00:00 to 7:00 PM using this:

    $start_time_db = $row['start_time'];
    $start_time = date("g:i A", $start_time_db);

    I have confirmed that the value of $start_time_db is actually 19:00:00.

    But when I display $start_time to my site it loses an hour and comes out as 6:00 PM instead of 7:00 PM. Any explanation on why and how to fix this?
    PHP time uses the server time offset including DST time modifications, MySQL's time function does not.

    Instead of directly modifying by a set amount you should be using DateTime's get_offset function so that you don't have to manually fix this twice every year.

    There are a number of other issues related to this based on problems in mysql with how it stores timestamps that, as far as I'm aware, have not been fixed in most live development cases. You can read up on some of those here but since in your case it's clearly just the DST offset I'd strongly recommend just doing what I mentioned above, especially over manually modifying the date with fixed numbers.

  7. #7
    SitePoint Enthusiast
    Join Date
    Sep 2008
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Clearly formatting during the SELECT would be more efficient. Not sure of how to do that in a SELECT clause like:

    $query = "SELECT * FROM classes WHERE day_of_week = '$class_day' ORDER BY start_date ASC";
    the values in the Table that I am extracting are start_time, start_date ,end_date, and a few others values like instructor, class name, location, etc.

  8. #8
    SitePoint Enthusiast
    Join Date
    Sep 2008
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Belsnickle - That was what was confusing me - DST. Of course.

    Thanks again.

  9. #9
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,837
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by RGStephens View Post
    Clearly formatting during the SELECT would be more efficient.
    You do that using the mySQL DATE_FORMAT() function within the call i place of the field name you are returning (where you currently have the *.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  10. #10
    SitePoint Enthusiast
    Join Date
    Sep 2008
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by felgall View Post
    You do that using the mySQL DATE_FORMAT() function within the call i place of the field name you are returning (where you currently have the *.
    Thanks. I'll rewrite that piece of code to incorporate this function. Thanks Again.


Bookmarks

Posting Permissions

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