2

Ok, i'm a bit confused. I am working on a project that have to store some time() in the database. It seems to me that for better flexibility i should store it as timestamp so that i could make operation between 2 times and other cool stuff. But i have seen (here more than everywhere else) that a lot of people store time in databases in date format, falling in some common questions like: how can i convert date format in timestamp? or how can i subtract a date to another that have different date format? etc.

Am i wrong using timestamps and convert it later (to be shown to the user) to time format? Mysql database.

Shoe
  • 74,840
  • 36
  • 166
  • 272
  • and which is the DB? :-) Gordon was faster! – acm Dec 07 '10 at 14:25
  • http://snippets.dzone.com/posts/show/1455 – Haim Evgi Dec 07 '10 at 14:25
  • Mysql database? Does it matter? Using timestamps in php is way more efficient than work on date format string. – Shoe Dec 07 '10 at 14:26
  • It depends... Different DB may have different ways of storing dates. If you plan on storing timestamps, then it does not matter since AFAIK all DBs support integer types... – acm Dec 07 '10 at 14:32
  • @andre A timestamp column in MySql is quite different to an Integer column. – Gordon Dec 07 '10 at 14:43
  • @Gordon, what I mean is that a timestamp can easily be stored to an integer type field (hence no db dependencies)... I'm not saying it's the same thing. – acm Dec 07 '10 at 14:50

3 Answers3

2

MySql supports DateTime, Date and Timestamp columns and offers a number of convenient functions to work with them through SQL. I suggest to read through the linked manual page to pick the right one for your needs.

Gordon
  • 312,688
  • 75
  • 539
  • 559
1

Just in case you didn't know, MySQL supports several date functions, DATESUB, DATEADD etc.

BenMorel
  • 34,448
  • 50
  • 182
  • 322
Srisa
  • 967
  • 1
  • 7
  • 17
  • 1
    The point is that you can do a lot of date arithmetic (taking care of timezones, date barriers, leap years, etc.) with SQL Date/DateTime data types, which may be even easier to use than having a lot of cryptic arithmetic in your `WHERE` clauses (in order to achieve the same with a timestamp). – svens Dec 07 '10 at 14:33
1

I tend to store timestamps in int(10) format, as a UNIX timestamp will always be 10 characters long. It's recommended to use int instead of varchar or char as when you use SQL's ORDER syntax it won't get the order incorrect.

Say you want to convert from a date to a timestamp, you could either use strtotime or mktime, depending on what you're doing.

To work out the difference between two timestamps just subtract them from one another.

For the issue of timezones you can set this to the user's country using date_default_timezone_set. This will be taken into account when you're using functions like date

RichW
  • 10,692
  • 6
  • 26
  • 33
  • 1
    The database has a native datatype that's not effected by the [y2k38 problem](http://en.wikipedia.org/wiki/Year_2038_problem) whereas storing timestamps is (since it's a 32 bit number)... – ircmaxell Dec 07 '10 at 14:41
  • Most web applications are built to only last a number of years, probably 5 at the maximum. There's only a handful of applications that need to use timestamps past the year 2038. I'd rather design the application to be efficient rather than completely future proof, it can always be upgraded closer to the time if it still existed. – RichW Dec 07 '10 at 14:54
  • 2
    That's the kind of thinking that got us into the y2k problem. While I agree that worrying about y2k38 now is silly, the solution exists, and it's no less efficient than your proposal (in fact, since the date arithmetic is done in compiled C I dare say it's likely to be far more efficient). So there's no real reason *not* to use the native datatypes... – ircmaxell Dec 07 '10 at 14:57