SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    May 2004
    Location
    NC
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    grouping unix timestamps by date

    I'm trying to find how many rows exist for each day in t1 and then return the highest count. (i.e. if there were 10 entries on 7-31-2004 and every other day had less, then "10" is the result I want.)

    SELECT COUNT(*) AS count FROM t1
    where date > $time1
    GROUP BY DATE_FORMAT(FROM_UNIXTIME(date), '%Y-%m-%e')
    ORDER BY count DESC LIMIT 1

    This query works correctly syntax-wise. The problem is that I'm using unix timestamps for the date and my server is in a different time zone(PST) from me(EST). The days are getting shifted by several hours and messing up the count.

    Now, I could fix it for myself by adding:
    (DATE_ADD(date,INTERVAL +3 HOUR))

    but this doesn't fix the problem for users in other time zones. Is there a good way to address this problem? Do I have to calculate the difference between each user's timezone and the server timezone? thanks in advance-

  2. #2
    He's No Good To Me Dead silver trophybronze trophy stymiee's Avatar
    Join Date
    Feb 2003
    Location
    Slave I
    Posts
    23,423
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Maybe if you use a standard time like GMT time your results will be all the same. Just a thought.

  3. #3
    SitePoint Member
    Join Date
    May 2004
    Location
    NC
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, the UNIX timetamps are in GMT. I'm using php putenv(TZ) to set the timezone for each user. The problem is that I can't figure out a good way to account for the server time zone difference in MYSQL.

  4. #4
    He's No Good To Me Dead silver trophybronze trophy stymiee's Avatar
    Join Date
    Feb 2003
    Location
    Slave I
    Posts
    23,423
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Maybe you shouldn't. Calculating for the user's timezone shouldn't be too much code so it wouldn't be a terrible thing to do after getting the info from the DB.

  5. #5
    SitePoint Addict pointbeing's Avatar
    Join Date
    Jun 2004
    Location
    London, UK
    Posts
    227
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In general I really don't like seeing dates stored in non-date types: you risk losing all those fantastic date handling functions that your RDBMS provides, you lose a great deal of efficiency, and if it's something like a unix timestamp, you lose the ability to 'read' that date with the human eye.

    But I see it done a lot - I still don't really know why.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by chillnc
    (i.e. if there were 10 entries on 7-31-2004 and every other day had less, then "10" is the result I want.)
    your time zone problem lies within the above statement

    how do you know there were 10 entries on 7-31-2004?

    when does 7-31-2004 begin and end?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    does not play well with others frezno's Avatar
    Join Date
    Jan 2003
    Location
    Munich, Germany
    Posts
    1,391
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by chillnc
    Code:
    SELECT COUNT(*) AS count FROM t1
    it's not part of your problem but i don't think it's good partice to use reserved words (in your case the count as alias of COUNT)
    We are the Borg. Resistance is futile. Prepare to be assimilated.
    I'm Pentium of Borg.Division is futile.Prepare to be approximated.


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
  •