SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Evangelist smftre's Avatar
    Join Date
    Dec 2008
    Location
    London
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    CONVERT_TZ Query

    Hello,

    I am capturing data (server is utc -5) so example timestamp would be: 2011-03-25T22:25:55-05:00.

    Now, that's all good and well when I return data like so: "SELECT * FROM `table` WHERE `timestamp` LIKE '2011-03-25'".

    But what happens if I want to return data from a different timezone?
    e.g UK would be 00:00 and not -05:00.

    See where I'm going with this?
    If only i could save a timestamp for each timezone, but that's overkill! there has to be a more realistic way!? apparently i could do it somehow with CONVERT_TZ in mysql?

    Can anybody shed any light?
    Statvoo.com The Website Traffic Monitor
    The best way to monitor traffic to your sites for free!


    Web Development London UK We make web 3.0 applications

  2. #2
    SitePoint Evangelist smftre's Avatar
    Join Date
    Dec 2008
    Location
    London
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Think I need something like the following, but obviously sql that actually works :P

    SELECT * FROM `table` WHERE `row`='4' AND `timestamp` LIKE '%(CONVERT_TZ(2011-04-07,'-05:00','00:00'))%'
    Statvoo.com The Website Traffic Monitor
    The best way to monitor traffic to your sites for free!


    Web Development London UK We make web 3.0 applications


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
  •