-1

Using MySQL should I store time data as an Int which would be the number of milliseconds since midnight, January 1, 1970 UTC or as Timestamp in the database.

I know both have advantages which is the best way to do it?

PriestVallon
  • 1,519
  • 1
  • 22
  • 44
  • whatever format you store it, always has the possibilities to convert it to another format. – devasia2112 Jun 12 '12 at 23:04
  • 2
    3 of the top 4 google results are to SO questions on the same topic, none of them answered your question? http://www.google.com/search?sugexp=chrome,mod=11&sourceid=chrome&ie=UTF-8&q=mysql+timestamp+vs+int – Mike Purcell Jun 12 '12 at 23:05
  • Is the TIME type not an option? That would seem to be the most straightforward. – Mark Wilkins Jun 12 '12 at 23:05
  • 1
    I personally use the DATETIME for all my time data.... Storing as an int is pointless... Haven't used Timestamp in YEARS.... DATETIME is where it's at... – Justin Jun 12 '12 at 23:08
  • 1
    When I was entering the question no related ones showed up so I assumed the question wasn't asked already – PriestVallon Jun 12 '12 at 23:08

2 Answers2

3

Can't say for sure from standpoint of your PHP code, but storing it in MySQL datetime format opens up all the MySQL date/time functions without having to deal with first converting with FROM_UNIXTIME. Also, timestamps have a limit of the year 2038 - see Why do timestamps have a limit to 2038?

As the comments say, you can convert either way, so it becomes a question of where do you want the most convenience - in your SQL statements, or PHP code.

IMHO, times/dates are not just a number - they have special purposes in our use of them, so storing them as just a number is limiting yourself.

Community
  • 1
  • 1
GDP
  • 8,109
  • 6
  • 45
  • 82
0

There are a couple of other things to think about when using timestamps in MySQL.

You are (NOT, see EDIT below) only allowed one timestamp column per table in your database

The one cool thing you can do with timestamp columns that you can't do with datetime columns is you can use default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP (again you only get one column that can do this).

Other than that and the other things mentioned already, I think that's the only incentive to use those columns.

EDIT: You can have more than one timestamp column in a table. But, you can only have one DEFAULT CURRENT_TIMESTAMP timestamp per table. Note that if you add a column to a table:

ALTER TABLE table_name ADD COLUMN col_name TIMESTAMP

and there isn't another timestamp column in that table, it implicitly adds that onto the definition. At least on MySQL 5.0.92. See the discussion below.

juacala
  • 2,155
  • 1
  • 21
  • 22
  • You are allowed to have more than one `TIMESTAMP` column per table. You are probably thinking that you cannot have more than one that has the `UPDATE CURRENT_TIMESTAMP` flag set – Matt Dodge Jun 13 '12 at 00:26
  • You're right that I misspoke. But what you said isn't exactly correct either. If you use DEFAULT CURRENT_TIMESTAMP, or ON UPDATE CURRENT_TIMESTAMP, then you can't have another timestamp column, even if it doesn't have that other stuff. I'll edit the post. – juacala Jun 15 '12 at 18:22
  • I'm not sure that's true, I just ran `CREATE TABLE table2 ( time1 timestamp NULL, time2 timestamp NULL ON UPDATE CURRENT_TIMESTAMP );` and it worked... – Matt Dodge Jun 15 '12 at 18:29
  • I see. You're right. What I wasn't aware of is that if you use a timestamp type with no modifiers (NULL/DEFAULT/etc.) it implicitly adds on the DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP. E.g. This query fails: `CREATE TABLE table3 ( time1 timestamp, time2 timestamp ON UPDATE CURRENT_TIMESTAMP );` I've reedited the original note. – juacala Jun 15 '12 at 21:51