How to create TIMESTAMPS for SQL query for it to be sortable by date from PHP?

Question

How to create TIMESTAMPS for SQL query for it to be sortable by date from PHP?

So I have table created by such SQL script

CREATE TABLE  `Keys` (
 `ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
 `KEY` INT NOT NULL ,
 `TIMESTAMP` DATE NOT NULL ,
UNIQUE (
`KEY`
)
)

What sript should I write to:

• Add timestamps
• To sort\search thru that timestamps

Answer 1

TIMESTAMP in MySQL will be returned to PHP as strings, in the YYYY-MM-DD HH:MM:SS format.

Which means you'll be able to use strtotime on them, to get an UNIX Timestamp, which is the "standard type" on which PHP works for dates/times.

You'll jsut have to consider that a timestamp is a number of seconds since 1970-01-01

Note there is a limit on timestamps (quoting) :

The TIMESTAMP data type has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

And that limit is generally the same on the PHP side.

Then, to manipulate those timestamps, on the SQL side, you can use a wide range of Date and Time Functions.

And they can be sorted like any other data-type -- you can also use the standard =, >, <, ... operators.

Answer 2

You might also just consider storing you dates as int... then use the date() function in php to display them.