Confused by Timestamp

[David_Wood]

I am a bit confused with the use of the timestamp column type.

My understanding (or lack of) was the if this column existed in a table then the current date/time of the last amendment was written to it.

I have such a column - formatted to 8 characters so that I just get the date - I am not concerned with the time and when I look at the data in the table it shows 20021002 which is today's date 2 October 2002.

So far so good. When I then try to output the data from a query formatted by using the date() function as date("d/m/Y", $row["tstamp"]) I am getting 20/08/1970.

Can anybody throw some light on this and relieve my confusion?

Many thanks
David Wood

[Sxooter]

You have unix / php timestamp mixed up with SQL timestamp.

In unix / php a timestamp is the number of seconds since the epoch, which is January 1 1970 I believe.

In SQL a timestamp is a date, and the format can change based on all sorts of things.

If you want to store a unix / php timestamp, just put it into an integer field. With postgresql, you could create a trigger or something that would update the integer with the unix timestamp when the row was inserted, or even when it was updated.

[jeremuck]

ereg ("([0-9]{4})([0-9]{2})([0-9]{2})", $date, $regs);

echo "$regs[2]/$regs[3]/$regs[1]";

// echo mm/dd/yyyy

This is modified. It works to change mysql date format (yyyy-mm-yy).

I took out the "-". So now it should change "yyyymmdd" into "mm/dd/yy".

If you want dd/mm/yy , switch the 2 and the 3.