mySQL timestamps

[Weirfire]

When you have a variable in a mySQL database which is of the timestamp format do you have to assign a value to the timestamp or does it do it automatically?

[digitalpoint]

It's a number... so just use an integer.

[Weirfire]

So would you recommend storing the date as a timestamp? :confused:

[digitalpoint]

Depends on if you need the time or not. If you just need the date (and not time), then it's not necessary. If you want both date and time, it's a good format.

[Weirfire]

Yes I need the time. :)

Thanks Shawn.

[mushroom]

Yes I need the time. :)

Thanks Shawn.
The first timestamp that is in a table is set to now(); on every insert or update automaticly even if you do not mention it.
Reads or updates that make no changes to the row leave the timestamp unchanged.

[Weirfire]

Thanks for your help Mushroom.

I've got it going now :D

[Ozz]

Use datetime instead... timestamp is terrible. As Mushroom said timestamp is updated automatically unless you declare the value in the query... if you forget its going to be a disaster... specially if you want to create logs or statistics... lets say that you missed this detail in the query and an entire set of rows goes withthe wrong data (it happened to me once... lucky me I backed up the database before :D).

In fact as I remember MySQL is going to change the way timestamps behave soon (in v4.1 or 5)... so again... its better to use datetime for me.

[aspcoder]

If you use MySQL timestamp, you must use MySQL function date_format() in the MySQL select command.like a ,

select date_format(now(),'%d-%m-%Y');

and you can replace the now() function with any column with timestamp type.