Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.

View Poll Results: How do you store dates in your database apps?

Voters
7. You may not vote on this poll
  • As a date or datetime data type

    5 71.43%
  • As an int data type and use program code to handle comparisons

    2 28.57%
  • I don't use dates.

    0 0%
Results 1 to 4 of 4
  1. #1
    Mega-ultimate member
    Join Date
    Jun 2002
    Location
    Winona, MN - The land of 10,000 lakes
    Posts
    1,855
    Thanks
    1
    Thanked 45 Times in 42 Posts

    Working with dates and databases

    At first glance, this may seem like a no brainer, but before you vote, please read this.

    A few months back I had the fortune of working with a client that stored all his dates in a database as an int type, instead of a date type. All the data was a unix time (epoch time) so it actually worked quite will if you set up a few date() and mktime() calls in PHP. But I just thought it was a noob mistake, dealt with it, then moved on.

    Now, I'm working on migrating an application I created using php and MS SQL server to a proper AMP environment (Apache, PHP and mySQL). Of course, my major problems lay around the DATEDIFF functions in SQL Server, and converting those queries and other queries using SQL SERVER only date functions to valid mysql date queries, then it hit me. If I had done all this with epoch time, this would have been a very simple migration.

    So, what do you think? When storing dates in a database, do you use date or datetime types or int type with epoch time and handle the comparisons in your code?

    I'd be interested to hear your responses.

  • #2
    Mega-ultimate member
    Join Date
    Jun 2002
    Location
    Winona, MN - The land of 10,000 lakes
    Posts
    1,855
    Thanks
    1
    Thanked 45 Times in 42 Posts
    Hmm, no responses to this. I would have thought there would be.

  • #3
    Regular Coder
    Join Date
    Feb 2005
    Location
    UK
    Posts
    133
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Usually I store the date in the database, but recently I've taken to storing the date as int, this means I can do things like SELECT * FROM table WHERE mydate < 1107827070 or something. It's really six of 1, half a dozen of another, really depends on what you want to do with the date.

  • #4
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I never understood why people convert dates to int, but thats probably because i'm to stupid or because these people have such extra-special requirements i never seem to encounter.

    all db's store dates as integers. using values in dateformat is basically just using another (= a better) representationformat of the actually used value.

    handling comparisons in your code? that's realy not efficient and not required at all. MySQL has a datediff() function so i don't see the problem. If you have an old version (pre 4.1.1), then you can just use a TO_DAYS() on the dates and subtract them.

    The only real argument i find in your post is that it would have made the migration simpler. unless you are planning on constantly migrating from one db-format to another, this is not realy such a big advantage... and doesn't ms server allows you to specify the outputformat you need (yyyy-mm-dd)?
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html


  •  

    Posting Permissions

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