4

I want to store the timestamp which a row was inserted into a MySQL table with the row, to be later compared to other times. What's the best field type for this and what's the best way to insert the row with the timestamp in PHP?

Chetan
  • 46,743
  • 31
  • 106
  • 145

3 Answers3

5

Use TIMESTAMP DEFAULT CURRENT_TIMESTAMP. This will create a TIMESTAMP field that is set on INSERT, but not on UPDATE.

Ignacio Vazquez-Abrams
  • 776,304
  • 153
  • 1,341
  • 1,358
  • When I retrieve the value with PHP, how do I make it return as the number of seconds since the reference date? – Chetan Dec 29 '09 at 07:56
  • @Chetan, SELECT UNIX_TIMESTAMP(updated_at) as updated_at FROM Table; – vava Dec 29 '09 at 07:59
  • In your `SELECT` query, add another column.. `now()-timestamp_column as difference` – Matt Dec 29 '09 at 08:00
  • @vava: That returns the unix timestamp value, not the difference in seconds since the current date. – OMG Ponies Dec 29 '09 at 08:00
  • I have a `SELECT *` query and I access the return values by index in the array that is generated. Is there any way to use the same `SELECT *` query without having to have a seperate `now()-timestamp_column as difference` query? – Chetan Dec 29 '09 at 08:04
  • Never mind, I can just use `strtotime()` to achieve the same effect. – Chetan Dec 29 '09 at 08:05
1

Use TIMESTAMP as the field type. All TIMESTAMP field types will have CURRENT_TIMESTAMP as the default (which is a alias for NOW())

While adding a record, write '' to that field - it will take the default time as the value.

KJ Saxena
  • 21,452
  • 24
  • 81
  • 109
0

Use the DateTime datatype for the column, the NOW() function to set the current time and the UNIX_TIMESTAMP() function if you need to compare it using PHP

Andrew G. Johnson
  • 26,603
  • 30
  • 91
  • 135