SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    difficulty formatting time value

    Currently when I search for this, sitepoint returns a blank page. And I couldn't see it in the mysql docs.

    I have a time col with times stored as 04:30:00

    Please can anyone help in how to format this to 4.30am and of course, to show pm, if appropriate.

    bazz

  2. #2
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, I got it by making a regex

    bazz

  3. #3
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)

  4. #4
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks guido. I read that but have only just seen what it means

    well, I have got the hours part but the other %k, %I etc don't seem to output what I would expect. Using the full range, they give back 12 0 12 12 12 for a time of 12:00:00

    I shall read it again later coz it's almost 7am here.

    bazz

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT TIME_FORMAT('21:37','%l:%i%p')
    
    9:37PM
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Arghhhh! I wasn't getting it to work because I had ' ' around the col name in the query.

    now, how do I make AM PM into am pm without post processing?

    I've tried the formatting 'codes' I am familair with but noe works. In this case it should, I thought, have been %P instead of %p

    bazz

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by IBazz View Post
    ...I had ' ' around the col name in the query.
    another unsuspecting victim of the misconception that anything (-cough- backticks -cough-) should ever be coded around a column name

    Quote Originally Posted by IBazz View Post
    now, how do I make AM PM into am pm without post processing?
    Code:
    SELECT LOWER(CAST(TIME_FORMAT('21:37','%l:%i%p') AS CHAR))
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    lol.

    No not backticks.. I have never used them. it was like this

    Code MySQL:
    SELECT TIME_FORMAT('$start_time','%l:%i%p')

    Thanks for the answer. wouldn't have got that any time soon.

    bazz

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    why would you feed in $start_time and get mysql to format it?

    why wouldn't you format it in your app instead?

    the only way the question made sense is if it was a column name

    you're not suppyling a variable column name are you?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    no, I'm not supplying a variable column name.

    the time is stored as 22:30:00 for example and I was trying to dislpay it as 10.30pm

    Are you saying that that is better done in my app code than at query stage?

    bazz

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by IBazz View Post
    Are you saying that that is better done in my app code than at query stage?
    as a general rule, absolutely yes, formatting of all sorts is best done in the application
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •