Thanks - very useful article. I want to store the date a record was created. Using Timestamp would work but everytime it updated the value is overwritten so I wouldn't have the date created. Can I set the default value of of datetime field to NOW() or something to that effect. At the moment I use PHP to insert the date created but it would be handier to have MySQL to do it instead.

Thanks,
Doug

Hi Doug,

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
Timestamp would work but everytime it updated the value is overwritten so I wouldn't have the date created.
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

I'll have to check on this but off the bat I would say create two timestamp fields. If memory serves me, MySQL will only update the first timestamp field on an update. I'll check and see if it sets the second one on an insert though.

Allan

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
I'll have to check on this but off the bat I would say create two timestamp fields. If memory serves me, MySQL will only update the first timestamp field on an update. I'll check and see if it sets the second one on an insert though.
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

OK, it was a nice try, but MySQL will only update the first timestamp field with either the insert or update. So you're left with manually setting the insert date with NOW() or with NULL - both will cause the timestamp field to get the current date and time.

When MySQL gives you triggers you'll be able to pop the current date and time into the field on insert and then not worry about it again.

So you need to have the insert and the update times on the record, or only the insert time? if only the insert time then just use a datetime field and initialise it to NOW() in the SQL that inserts the record.

Allan

Thanks for clearing that up for me Allan! <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>