• 0

Storing Dates in the Database


Storing dates in the database  

8 members have voted

  1. 1. Datetime or Unix Timestamp

    • Datetime
      4
    • Unix Timestamp
      3
    • Other
      1


Question

Hey,

So I'm working on this project, and was trying to figure something out.

How should I store the dates in the database (MySQL).

 

Now, normally, I just used time() (Seconds since 1970). I do it for all dates. Dates when people registers, blog post dates, anything like that. I like working with them. I find it easy.

 

But, what is more recommended. I tried Google, but seems that it doesn't matter if you use the date, or unix_timestamp. But I thought I would see what the people of Neowin think.

Link to comment
Share on other sites

8 answers to this question

Recommended Posts

  • 0

I don't use MySQL regularly, but I have used other SQL databases extensively and after 15 years of design and support experience, the definitive answer is:

 

Test both and use what works best for your project.

 

 

 

Seriously, that's it, use the right tool for the job. Don't over engineer.

  • Like 2
Link to comment
Share on other sites

  • 0

I don't use MySQL regularly, but I have used other SQL databases extensively and after 15 years of design and support experience, the definitive answer is:

 

Test both and use what works best for your project.

 

 

 

Seriously, that's it, use the right tool for the job. Don't over engineer.

That's why I generally use timestamps..I've worked with other scripts that all seem to use datetime, and I find it more annoying to work with...I find timestamps so much easier to work with..But I've noticed that more scripts seem to use datetime over timestamps...So figured I would see what others on here use.

Though very good advice, thanks!

Link to comment
Share on other sites

  • 0

If I were you then I would store the UTC Timestamp.

 

Again, it really depends on the project. I use datetime for everything except when I don't.

Link to comment
Share on other sites

  • 0

Haven't used MySQL in forever, but I'd say look at them all and see which one offers the lowest grain you'll actually use. Don't use a date-time format that stores the millisecond if all you need is the second. Don't use a datatype that uses 8 bytes when you only need on that uses 4. 

Link to comment
Share on other sites

  • 0

For me, it's the timestamp every time. It makes sorting by date easier (and faster). It's also (slightly) less computationally expensive to go from timestamp -> date/time than it is to go from date/time (as a string) to a timestamp.

Link to comment
Share on other sites

  • 0

Again, it really depends on the project. I use datetime for everything except when I don't.

True. I wasn't sure if O.P. was taking about dates in general or the auditing info for a record. If it is dates in general then I would say depends but if it is for auditing then I would go for UTC.

Link to comment
Share on other sites

This topic is now closed to further replies.