SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    .* draziW tnioPetiS *. bronze trophy
    Join Date
    Jun 2004
    Location
    "Then I figure the most good good guy will win."
    Posts
    1,666
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need to convert MySQL timestamp to local UTC timestamp?

    Gurus! I need your help.

    My MySQL database returns a timestamp formatted like so:

    Code MySQL:
    1239752639

    When testing:

    Code PHP:
    echo date('m/d/y, g:ia', 1239752639) . "\n"; // DB
    echo 'VS' . "\n";
    echo date('m/d/y, g:ia', time()) . "\n"; // Current.

    The above outputs:

    Code HTML4Strict:
    04/14/09, 4:43pm
    VS
    04/14/09, 10:37am

    As you can see, the DB time is ahead of the local time.

    I am sure that I am missing something obvious here, but what is the best way to convert the database timestamp into a local UTC timestamp?

    Many thanks in advance!

    Cheers,
    Micky

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I don't know what's up with the 6 minutes, but if it's 6 hours ahead of your preferred timezone, subtract 6 hours.

    PHP Code:
    $timestamp $timestamp - (60 60); 

  3. #3
    .* draziW tnioPetiS *. bronze trophy
    Join Date
    Jun 2004
    Location
    "Then I figure the most good good guy will win."
    Posts
    1,666
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Dan!

    Thanks for the reply, I really really really appreciate the help.

    $timestamp - (6 * 60 * 60);
    Ahhh, that simple! Nice.

    I guess I was assuming that I should account for the timezone diff by using the server.

    Should I worry about daylight savings time?

    Man, this time/date stuff makes my head spin!

    Anyway, your solution will work great!

    You ROCK Dan!!!

    Edit:

    I wonder if I should be doing the time conversion via the sql? "SELECT last_entry_date ..."


    Cheers,
    Micky

  4. #4
    .* draziW tnioPetiS *. bronze trophy
    Join Date
    Jun 2004
    Location
    "Then I figure the most good good guy will win."
    Posts
    1,666
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi!

    Just as an update, the code Dan provided works perfectly:

    Code PHP:
    <?php $timestamp - (7 * 60 * 60); ?>

    Thanks again Dan.

  5. #5
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by mhulse View Post
    Edit:

    I wonder if I should be doing the time conversion via the sql? "SELECT last_entry_date ..."
    MySQL has timezone conversion functions, you can do it there as well.

    http://dev.mysql.com/doc/refman/5.1/...ion_convert-tz

  6. #6
    .* draziW tnioPetiS *. bronze trophy
    Join Date
    Jun 2004
    Location
    "Then I figure the most good good guy will win."
    Posts
    1,666
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dan Grossman View Post
    Oooh, cool! Thanks again Dan!

    I definitely needed a kick in the right direction on this one.

    Have a great day!
    Cheers,
    Micky

  7. #7
    SitePoint Evangelist simshaun's Avatar
    Join Date
    Apr 2008
    Location
    North Carolina
    Posts
    438
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In MySQL, is the timestamp field of the "timestamp" type?
    If so, MySQL timestamps are not the same as the UNIX timestamps that PHP works with.

    To convert it in the sql, you can do something like this:
    PHP Code:
    $result mysql_query("
        SELECT
            UNIX_TIMESTAMP(timestamp_field) AS unix_time
        FROM
            table
        WHERE
            .........
    "
    );
    $row mysql_fetch_assoc($result);
    echo 
    date('m/d/y, g:ia'$row['unix_time']) . "\n"// DB
    echo 'VS' "\n";
    echo 
    date('m/d/y, g:ia'time()) . "\n"// Current. 

  8. #8
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by simshaun View Post
    In MySQL, is the timestamp field of the "timestamp" type?
    If so, MySQL timestamps are not the same as the UNIX timestamps that PHP works with.
    He gave an example of what his query is returning, 1239752639, which is already a UNIX timestamp for today.

  9. #9
    .* draziW tnioPetiS *. bronze trophy
    Join Date
    Jun 2004
    Location
    "Then I figure the most good good guy will win."
    Posts
    1,666
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi simshaun!

    Quote Originally Posted by simshaun View Post
    In MySQL, is the timestamp field of the "timestamp" type?
    If so, MySQL timestamps are not the same as the UNIX timestamps that PHP works with. <snip>
    Ahhh, very interesting! Thanks for the code and clarification! I definitely learn best by first-hand example.



    Quote Originally Posted by Dan Grossman View Post
    He gave an example of what his query is returning, 1239752639, which is already a UNIX timestamp for today.
    That is what I assumed they are... I am using a CMS called Expression Engine, and I guess it stores dates in the DB in the (what I assumed was) UTC format "1239752639".

    The EE CMS handles a the TZ conversion for almost all my needs. Just, in this instance, I had to break out of the EE template code and use a custom query.

    Sorry if I used any of the wrong terms for this date stuff... Like I said, it makes my head spin!

  10. #10
    SitePoint Evangelist simshaun's Avatar
    Join Date
    Apr 2008
    Location
    North Carolina
    Posts
    438
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Great choice of CMS. EE is favorite next to my own.

    Anyway, EE stores all timestamps in UTC(GMT) using an INT field in MySQL.
    So, all you need to do is offset the time by however many hours you are away from UTC.

    See this entry for EE's date localization.

  11. #11
    .* draziW tnioPetiS *. bronze trophy
    Join Date
    Jun 2004
    Location
    "Then I figure the most good good guy will win."
    Posts
    1,666
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by simshaun View Post
    Great choice of CMS. EE is favorite next to my own.
    Ah, cool! Yah, I love EE... I use Textpattern as my second choice. Do you have a link to your CMS?

    Quote Originally Posted by simshaun View Post
    Anyway, EE stores all timestamps in UTC(GMT) using an INT field in MySQL.
    So, all you need to do is offset the time by however many hours you are away from UTC. See this entry for EE's date localization.
    Oh, great info!

    This is the first time I have had to really go outside of EE templating system to get the data I need...

    Anyway, thanks thanks thanks!!!

  12. #12
    SitePoint Evangelist simshaun's Avatar
    Join Date
    Apr 2008
    Location
    North Carolina
    Posts
    438
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Do you have a link to your CMS?
    My CMS isnt public. Its sort of an off-shoot of EE, minus some features most of my clients never need, plus some others. I'm already well into development on a new one too, which incorporates some of my favorite aspects of EE with some other CMSs I've reviewed.

  13. #13
    .* draziW tnioPetiS *. bronze trophy
    Join Date
    Jun 2004
    Location
    "Then I figure the most good good guy will win."
    Posts
    1,666
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by simshaun View Post
    My CMS isnt public. Its sort of an off-shoot of EE, minus some features most of my clients never need, plus some others. I'm already well into development on a new one too, which incorporates some of my favorite aspects of EE with some other CMSs I've reviewed.
    Ahh, very cool! Built using Code Igniter? I see you are linking to CI in your sig. Looks like a nice framework. I have been using EE for years, and (in last few years) have wondered how much better, or different, the Elis Lab framework is...

    I think for my next personal website I will try using a PHP/Python (Django) framework. As much as I love EE and TXP, I would really prefer something more homegrown and lightweight.

    Thanks again simshaun and Dan! I owe you guys a brewski!

  14. #14
    SitePoint Evangelist simshaun's Avatar
    Join Date
    Apr 2008
    Location
    North Carolina
    Posts
    438
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The new one I'm creating is built on top of the CI framework and heavily utilizes the jQuery/jQuery UI framework.

    CI really is an excellent framework, and I'd recommend it to anybody in a heartbeat.
    ExpressionEngine 2.0, which is under development, is built on top of the CI framework.
    I am anxious to see how well it performs when it comes out.

    Another CMS that I think is pretty cool, but I like less than EE, is ModX.

  15. #15
    .* draziW tnioPetiS *. bronze trophy
    Join Date
    Jun 2004
    Location
    "Then I figure the most good good guy will win."
    Posts
    1,666
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by simshaun View Post
    The new one I'm creating is built on top of the CI framework and heavily utilizes the jQuery/jQuery UI framework.

    CI really is an excellent framework, and I'd recommend it to anybody in a heartbeat.
    ExpressionEngine 2.0, which is under development, is built on top of the CI framework.
    I am anxious to see how well it performs when it comes out.
    Ooooh, so cool! Yah, it probably would be smart for me to start using the framework so I can be on top of my EE game for when 2.0 comes out! I have been looking forward to v2.

    I also love jQuery (who doesn't????)

    Hehe, you have inspired me to learn something new. Thanks!!!

    Quote Originally Posted by simshaun View Post
    Another CMS that I think is pretty cool, but I like less than EE, is ModX.
    Oh, yah! I have heard good things about that CMS also! Thanks for the recommendation.

    Thanks simshaun!

    Cheers,
    Micky


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
  •