Page 1 of 1
safe to use timstamps as id's ?
Posted: Wed Oct 15, 2003 8:13 am
by mzfp2
Hi
I get very tempted when conctrsucting queries in PHP, to use timestamps as ID values for database rows, as this means i dont have to create two queries when inserting a row, one to get MAX id to add one too, in order to obtain the new id, and one query to insert the data using this id.
Anyway basically, is it safe to use timestamps as identification values, would there ever be a situation where two timestamps would be identical?
Thanks
http://alljammin.com
Posted: Wed Oct 15, 2003 9:07 am
by twigletmac
Which database do you use? Can't you use a autoincrementing ID value?
Timestamps (assuming you mean UNIX ones) could be identical if two records are submitted within the same second. Personally I would use an autoincrementing ID value, thus removing the need for two queries and ensuring that the same ID cannot be given twice.
Mac
Posted: Wed Oct 15, 2003 11:18 am
by mzfp2
i'm using MYSQL, im not sure how to use auto incrementing values in MYSQL, and it would also mean i'd have to change alot of tables, but i'll check out MYSQL documentation anyway
thanks

Posted: Wed Oct 15, 2003 11:43 am
by microthick
Setting up auto-increment id fields in MySQL is a cinch, especially if you use MySQL Control Center (
http://www.mysql.com/products/mysqlcc/index.html).
In MySQL CC it's just a matter of making your id field your primary key, then selecting the AUTO INCREMENT checkbox.
Posted: Wed Oct 15, 2003 6:00 pm
by itbegary
And after you have inserted the value run this statement "SELECT last_insert_id() AS lastid" and then read the resultset. This isn't that expensive of an operation as this value is present on the open connection to MySQL.
According to something I read somewhere this value is good for each conenction, meaning that if you have to apps, pages, whatever, inserting at the same time each will get the proper value because it's part of the original connection.
As for the timestamps I would say away from them. It is possible to get a duplicate and on 2030 you'll be screwed (unless you have upgraded since then) because that's when the timestamp field rolls over to 00000000000. This is not true on the date or date/time fields though.
Posted: Thu Oct 16, 2003 9:45 am
by m3rajk
assuming you didn't make the table:
create table blah(
id_field bigint NOT NULL auto_increment PRIMARY KEY,
some_field int,
another_field text
) TYPE=MyISAM;
anything italicised is a name you have ot make up.
that creates a simple table with three fields, one is a primary key (optional) and the auto-incremented field
if the table is created
alter table balh2 modify id_field bigint NOT NULL auto_increment PRIMARY KEY
i added the primary key again becuase i use id feilds as auto incremented primary keys
Posted: Thu Oct 16, 2003 10:01 am
by mzfp2
thanks for all the lp, that bit about timestamp rolling back to 00000000 on 2030 was particularly interesting, will slowly have to work around my system to fade them out ! thats if im still doing this in another 26 years !