SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    Apr 2002
    Posts
    90
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    php date() vs. mysql date_format()

    which one of these would have faster execution time? i would assume using the date_format() function within the mysql query would yield faster execution, but just curious if any one knows for sure. and as a general rule, is it better to use mysql functions in a query when possible?

    thanks!

  2. #2
    gingham dress, army boots... silver trophy redux's Avatar
    Join Date
    Apr 2002
    Location
    Salford / Manchester / UK
    Posts
    4,838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    although i have no test data to back this up, i would say the mysql way is slighly quicker. i usually tend to do what i can in the query, as i keeps the actual php code a lot cleaner, and it's easier to maintain (or if you're switching db at a later stage). just my $0.02 worth of advice. might be worth writing a test suite, doing a select of 1000 records in both methods and clocking them...but at the moment i can't be physically asked, sorry
    re·dux (adj.): brought back; returned. used postpositively
    [latin : re-, re- + dux, leader; see duke.]
    WaSP Accessibility Task Force Member
    splintered.co.uk | photographia.co.uk | redux.deviantart.com

  3. #3
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    wouldn't you use PHP's date() for Unix timestamps (e.g. timestamps stored in an INT) and DATE_FORMAT() for dates/times in DATE/TIME/DATETIME/TIMESTAMP columns? so it depends on how the date is stored.
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

  4. #4
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Or
    PHP Code:
    DATE_FORMAT(FROM_UNIXTIME(datefield), '%y-%m-%d')); 
    to format your unixtimestamp in the query.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  5. #5
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    according to the manual, you can just do this too:

    Code:
    FROM_UNIXTIME(datefield, '%y-%m-%d')

  6. #6
    SitePoint Enthusiast
    Join Date
    Apr 2002
    Posts
    90
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks for the replys! i think i will try and do my date formatting in the SQL query because it seems to make the most sense to query and end up with data exactly how i want it.

  7. #7
    Grumpy Mole Man Skunk's Avatar
    Join Date
    Jan 2001
    Location
    Lawrence, Kansas
    Posts
    2,067
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't know what the generally accepted "best practise" for this is, but I always deal with date formatting at the last possible moment (i.e in the code that deals with final presentation to the user) as date formatting is fundamentally a presentation issue. Now that I've started using Smarty as my final template system I deal with date formatting in a Smarty template tag as that completely seperates the decision of how the date should be displayed form the "business logic" and database interactions. This is a great principle if you are working with more complicated three tier architecture sites but for small sites I don't see any reason not to format the date in the SQL query. As far as performance goes unless your traffic is high enough to put a significant load on your server (say more than 2 or 3 hits a second) it won't make any difference either way. If you ARE getting that kind of high traffic you should look at benchmarking to find out which has the best performance (but then you should probably also be looking at some kind of three tier architecture )


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
  •