SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Zealot
    Join Date
    Apr 2005
    Posts
    139
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Order by Time problem

    Hi,

    Im have a Time field that im using to sort the records chronologically. Im storing the 12pm-24hr time as 00hr, since there isnt a real time of 24hr.
    So, the problem is that when i Order the info by the time field the 00hr records appear at the start of the list instead that at the end (after the 11:59pm). I want them at the end because im using them as the End of the day, not the start of one.

    Im ordering the field with this:
    Code:
    ORDER BY coalesce(time, 24)
    What can i do to fix this?
    Should i be storing the 12pm as 24hr? (i tried that, but the order by showed another value for the 24hr time)

    Thanks!
    Nico

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    what datatype is the time column? int?

  3. #3
    SitePoint Zealot
    Join Date
    Apr 2005
    Posts
    139
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Datatype is: Time (HH:MM:SS).

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    let me ask you: do you also consider january 1st as the end of december?

    if not, then i suggest you "go with the flow" and consider 00:00:00 as the start of the new day
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Apr 2005
    Posts
    139
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I agree with you, 00:00:00 is the start of a new day, no questions about that. But there also moments when you refer to 00:00:00 hr as 24hr just to make it obvious that you are talking about the end of the day. Like in some bars that are open from 7 to 24 or something. We know that 24hr doesn't exist really and it's just another way of refering to 00:00.

    Based on your comment i guess im not going to be able to do what i want. So maybe i should store the last hr of the day as 23:59 instead of 00:00 or 24:00 ?

  6. #6
    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 nico7799 View Post
    Based on your comment i guess im not going to be able to do what i want. So maybe i should store the last hr of the day as 23:59 instead of 00:00 or 24:00 ?
    Sure you can do what you want. Store it correctly (00:00 as the start of the day, and 23:59 as the end) and adjust your display when you pull it out. Nobody sees raw result sets when they use your app


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
  •