Results 1 to 11 of 11

Thread: Sorting by Time

  1. #1
    Join Date
    Feb 2004
    Posts
    41

    Unanswered: Sorting by Time

    I have a field in my sql db that contains Time stored like: 9:00 AM and 1:00 PM.

    I need to be able to sort this field. When I use the ORDER BY clause, it puts 1:00 PM ahead of 9:00 AM, because 1 comes before 9 I am guessing. I can't use military time, but I am thinking I may have to store it that way, then convert it?

    So the questions is, how best to store the time, so that the ORDER BY clause will work as I need it too?

    Any help, examples, directions to a good example would be greatly appreaciated.

    Thanks,

    Mark

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the best way to store a time, independent of a date, will depend on which database you are using

    which database are you using?

    you could also use a string datatype, such as VARCHAR, if you store your times in 24-hour format, e.g. 09:00 and 13:00
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    If your database system allows it, you may
    Code:
    ORDER BY substring(field,7,2), field
    (or whatever function takes substrings).
    This will only work if all the "AM" and "PM" align, i.e., if there is a blank before 1: up to 9: but no blank before 10: up to 12:

    But most likely your RDBMS supports the "TIME" type, in which case it's better to store the data as such. Functions will probably exist to convert back and forth between AM/PM notation and internal TIME representation.
    Last edited by Peter.Vanroose; 10-20-06 at 19:33.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    are you certain your database is storing the time as "HH:MMS ?M"

    sounds like bad physical design to me

    I'd check that the db was actually storing data in that format (check the column defintion). if it is storing the data in a string / text format then personally Id want to grasp the designer of the db warmly by the throat (using both hands, natch), and then change the storage mechanism to use a time or date / time value. if needs be duplicate the data (if the exisitng desing uses this time value elsewhere and you cant make changes) or check to see if the db I was using allowed for a date/time conversion as part of the query - this would be inefficient and slow trhe query, especailly if used as part of a where OR ordere criteria.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by healdem
    change the storage mechanism to use a time or date / time value.
    Indeed.
    One transparant way to make this change is the following:
    1. Create a new table with the changed structure.
    2. Lock the old table (so no new changes come in) and insert into the new table with a select from the old one. The query to do so is not too difficult to write.
    3. Drop the old table.
    4. Create a view with the name of the old table, which refers the new table, and which does the opposite conversion. (Again, a relatively simple query, depending on the RDBMS you use.)
    5. Optionally: write a trigger to allow inserting into the view. Use the query from step 2 to do so.

    All applications that were reading the old table will keep working. They can be gradually rewritten to use the new table instead.
    Such a rewrite will typically improve the performance considerably.
    Applications writing the old table will possibly fail, if (5) cannot be implemented. This depends on your RDBMS.
    Such applications will have to be rewritten a.s.a.p.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Quote Originally Posted by healdem
    Are you certain your database is storing the time as "HH:MM:SS ?"
    Not that it matters much in this case, but I couldn't resist: such a format is likeliy to raise an exception in Oracle (in some cases, when 'MM' value is larger than 12):
    Code:
    SQL> select to_date('09:50', 'hh:mm') from dual;
    select to_date('09:50', 'hh:MM') from dual
                   *
    ERROR at line 1:
    ORA-01843: not a valid month
    because 'MM' format means month, not minutes; we (Oracle users, that is) use 'MI' instead
    Code:
    SQL> select to_date('09:50', 'hh:MI') from dual;
    
    TO_DATE(
    --------
    01.10.06
    Was it slip of a tongue or do some databases really use 'MM' as a minute format mask, I wouldn't know (fast Googling session suggests 'MI', but that doesn't have to mean anything).

  7. #7
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by Littlefoot
    do some databases really use 'MM' as a minute format mask
    DB2 manuals use the "hh:mm:ss" notation, but DB2 has no syntactic need for it. E.g., adding 1 hour and 20 minutes to a time, DB2 users say "timeval + 1 HOUR + 20 MINUTES".
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    has anyone noticed that in post #2 i asked which database the original poster was using, and he has not yet replied
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Certainly, Rudy; but that is not unusual (I mean, not coming back and leaving other people develop all kinds of theories ).

    Peter, I'm not familiar with DB2. I know this isn't an issue here, but - if you have some time, do respond - how would I, in Croatia, add 1 hour and 20 minutes? I guess DB2 also has some kind of "national language support" enabled and, in Croatia, I would add "1 sat 20 minuta" which is different from "1 hour 20 minutes". Would that work regardless my NLS, or should one always, by default, use "n hour m minutes"?

  10. #10
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by Littlefoot
    "1 sat 20 minuta"
    No, that wouldn't work, as far as I know.
    I'm not a native English speaker neither, but still I've got used to see all "strictly syntax related" things in English.
    That's an advantage, by the way: table and column names can be written in Dutch (or Croatian), while syntax-related words like "COUNT(*)" or "GROUP BY" are in English.
    It helps in distinguishing between the two.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  11. #11
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I see; so, "1 hour 20 minutes" is to be understood as DB2 keywords and is not subject of national language support.

    Of course, COUNT, GROUP BY etc. weren't part of the discussion. Those I understand

    Thank you for the explanation!

Posting Permissions

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