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?
A:
Use TIMESTAMP NOT NULL
When you do an INSERT and leave out that column, it will default to CURRENT_TIMESTAMP
Matt
2009-12-29 07:47:57
+1, Why was this down-voted?
Alix Axel
2009-12-29 08:01:57
I didn't downvote, but `TIMESTAMP NOT NULL` only means that the column can never have a null value; you need a `DEFAULT` constraint to ensure the column is populated on insert.
OMG Ponies
2009-12-29 08:03:15
A:
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
2009-12-29 07:49:01
A:
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.
Crimson
2009-12-29 07:53:24
+3
A:
Use TIMESTAMP DEFAULT CURRENT_TIMESTAMP. This will create a TIMESTAMP field that is set on INSERT, but not on UPDATE.
Ignacio Vazquez-Abrams
2009-12-29 07:54:27
When I retrieve the value with PHP, how do I make it return as the number of seconds since the reference date?
Chetan
2009-12-29 07:56:39
In your `SELECT` query, add another column.. `now()-timestamp_column as difference`
Matt
2009-12-29 08:00:09
@vava: That returns the unix timestamp value, not the difference in seconds since the current date.
OMG Ponies
2009-12-29 08:00:42
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
2009-12-29 08:04:44