SitePoint Sponsor

User Tag List

Page 2 of 2 FirstFirst 12
Results 26 to 42 of 42
  1. #26
    SitePoint Addict tbakerisageek's Avatar
    Join Date
    Sep 2006
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    AAAAhhhhhhhh..... That's why I asked. I wasn't aware that there was such function in MySQL

  2. #27
    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)
    Quote Originally Posted by tbakerisageek View Post
    I guess I'm not understanding what you mean by "the (12) and (10) only affect display formatting and is completely unrelated to storage requirements"
    in PHP, the (12) and (10) is completely ignored. AFAIK, the only program that honors those formatting rules in the mysql CLI. the mysql manual calls this value "length" and it has nothing to do with the storage requirements or the maximum allowable values.

    INT takes 4 bytes. the allowed values are -2147483648 to 2147483647, or 0 to 4294967295 for UNSIGNED.

    see http://dev.mysql.com/doc/refman/5.0/...ric-types.html and http://dev.mysql.com/doc/refman/5.0/...uirements.html

    experience must be moderated by knowledge of the documentation, and effective use of documentation is difficult without experience.

  3. #28
    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)
    Quote Originally Posted by arkinstall View Post
    Anyway, I cast you all back to a thread started 2 years (just 3 days out) ago, when a similar conversation was to be discussed (Longneck was there!):
    http://www.sitepoint.com/forums/showthread.php?t=306686
    ah, i think i summed it up quite nicely in that thread:
    Quote Originally Posted by longneck
    datetime. you will then be able to use the built-in mysql functions to do date calculations. but if the only think you ever need to do with the columns is compare using =, > or < then unix timestamps are ok.

    my personal preference is to use datetime columns because you can do everything you can do with unix timestamps, plus more without the overhead of converting them first.
    plus when you're browsing your database with a query tool, you can actually read your dates!

  4. #29
    SitePoint Addict tbakerisageek's Avatar
    Join Date
    Sep 2006
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck View Post
    plus when you're browsing your database with a query tool, you can actually read your dates!
    I have to give you credit on that one... I revert to using a unix timestamp calculator extension for firefox all the time.

  5. #30
    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)
    you could also just write your queries with from_unixtime()

  6. #31
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,868
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    The more work you can offload from PHP into the SQL the more efficiently your program will run. The worst code the database will use to do what you ask is the same as the PHP you would write yourself and often the SQL can find a more efficient way of doing it during the actual search and retrieval process.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  7. #32
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by wonshikee View Post
    What is a real-life example that shows datetime is better to use?
    here are three
    • retrieve all sales for the current month, up till today at noon
    • retrieve all sales which took place on a tuesday
    • find all birthdays in the next 20 days
    make sure that you return only the desired rows, i.e. do not return all rows in the table to php in order to decide which rows you want
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #33
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    here's another reason: store birthdays before 1970, or due dates after 2038
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #34
    SitePoint Addict tbakerisageek's Avatar
    Join Date
    Sep 2006
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I see the validity there, but I could write a query to fit each of the given examples that would return only the data I want from the table. Not retrieve all of it and then parse only what I want.

  10. #35
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    okay, another reason: when browsing data through a front-end like phpmyadmin, you can't tell what date the stupid integer is just by looking at it

    that should be your tie-breaker right there
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #36
    SitePoint Addict tbakerisageek's Avatar
    Join Date
    Sep 2006
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I feel like there are a lack of people on my side!!!!!

    If someone can post an example of how to do a diff between dates in a functional way, I think I'm convinced to change up...

  12. #37
    Obey the Purebreed trib4lmaniac's Avatar
    Join Date
    Dec 2004
    Location
    Cornwall, UK
    Posts
    594
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by tbakerisageek View Post
    If someone can post an example of how to do a diff between dates in a functional way, I think I'm convinced to change up...
    The only "diff" that you can easily do with timestamps is a simple subtraction of two such values, resulting in the number of seconds between them. So I am assuming you are asking how to do that with DATETIME values.
    Code MySQL:
    SELECT TIMESTAMPDIFF(SECOND, '2007-10-08 11:30', '2007-10-11 18:45');
    -- Returns 285300
    Of course, as you are using DATETIMEs now, you can even be flexible about what you get in return.
    Code MySQL:
    SELECT TIMESTAMPDIFF(HOUR, '2007-10-08 11:30', '2007-10-11 18:45');
    -- Returns 79
    And if all that has been mentioned in this thread already doesn't convince you that DATETIMEs are worthy of our usage, then remember that you can always use UNIX_TIMESTAMP should you ever have need to revert to your old ways (which you won't ).
    Code MySQL:
    SELECT UNIX_TIMESTAMP('2007-10-08 11:30')
    -- Returns 1191839400
    Of course, this naturally implies that you can perform the original calculation (difference in seconds between two times) like this:
    Code MySQL:
    SELECT UNIX_TIMESTAMP('2007-10-11 18:45') - UNIX_TIMESTAMP('2007-10-08 11:30')
    -- Returns 285300

  13. #38
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by tbakerisageek View Post
    it doesn't have to be int(12) it only needs to be int(10)... two bytes shorter per record for 10,000 records or more adds up quickly!!!
    actually, no, that's wrong

    INT(10) is the same as INT(1) is the same as INT(937) -- they're all just 4 bytes

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #39
    SitePoint Enthusiast monkey56657's Avatar
    Join Date
    Jun 2007
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    When you get down to it even with the datetime format behind the scenes MySQL is storing it as a number anyway right ? The only advantage of using the date datatype is for easy changing and viewing without any conversion if you ask me...so yeah I use and will always use the INT(10) or just INT(11) if i get too lazy to type 10 into the box on phpmyadmin

  15. #40
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by monkey56657 View Post
    or just INT(11) if i get too lazy to type 10 into the box on phpmyadmin
    since you are lazy, why not just type INT
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #41
    SitePoint Wizard TheRedDevil's Avatar
    Join Date
    Sep 2004
    Location
    Norway
    Posts
    1,198
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by felgall View Post
    The more work you can offload from PHP into the SQL the more efficiently your program will run. The worst code the database will use to do what you ask is the same as the PHP you would write yourself and often the SQL can find a more efficient way of doing it during the actual search and retrieval process.
    This is not always true. It is cheaper to scale php than mysql. So depending on your application it can be both cheaper and preferred to let php do some more work.

    Personally I use both. DATETIME when the result is not used only by the script and it makes more sence to use it compared to a timestamp. Sometimes you have to use it, forexample if your going outside of the timestamps range.

    That said, when considering application performance and load required its sometimes wise to use timestamp. Especially if these values are only used within the script itself to do calculations and never displayed.
    Why add an additional convertion if its not needed?

  17. #42
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    What I tend to do is store the strings for all of the mysql queries I'm likely to use in an application.
    Because I do this, I don't want to have to convert it all from a timestamp to a date when it comes to fetching a date/time result, therefore I use DateTime. It means that I can store repeditive queries, and upon calling them I imediately get the results I'm after - without conversions.

    Quote Originally Posted by kon-tiki, but modified a bit
    Beat that with a point stick using a timestamp
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona


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
  •