How to strtotime a timestamp, OR how best to deal with SQL dates?

In my PHP app, I get dates from the MySQL database, when I format them with PHP's date() command. I want to tidy that up and move it all entirely to the view, rather than the retrieval. But I ran into some problems.

When getting a date from the database, I pass it to the rest of the app after running strtotime() on it, so that the date() functions have something they can work with. Is this a good idea? or am I missing something better and obvious? The dates that I'm dealing with are nearly all in the DATE type, not DATETIME.

So, I get the date, and can use date() to display it on the webpage. But sometimes, I'll get something from the database solely to insert elsewhere in the database, and having to hunt through the backend to find places that need date() kind of defeats the purpose of separating it out to the view. My retrieval functions helpfully return strtotime($date_from_db), and then I create the new entry into the other table... and all new entries must pass through the validation and preparation functions. And herein lies the probl开发者_运维技巧em. I validate and prepare dates by seeing if they strtotime() to something other than 0000-00-00. But I can't run strtotime() on a timestamp; in the test I ran, if gave me 12/31/1969. So my questions:

1. Is it possible somehow to run strtotime() on a timestamp?
2. If not, is there an easy way to know if a variable is a timestamp? So I could simply run date() on it rather than dealing with strtotime()? Apart from seeing if it comes out to 12/31/1969, because I don't know if I can trust a failure to be consistent. Unless that's a valid thing to do anyway?
3. And am I doing this completely wrong?

As a general approach, I'd be very tempted to :

1. Store date/time values using MySQL's datetime type, as this will allow you to meaningfully query the data within MySQL without having resort to either using >= "timestampvalueX", etc.

2. Keep all "live" date information using within PHP scripts in timestamp format.

As such, you probably want to aim to store all of the date/time related information in that format as a goal. You can the trivially convert this into a format PHP can use either in the query itself (using UNIX_TIMESTAMP) or by exploding the YYYY-MM-DD HH:MM:SS format and using PHP's mktime to create a "standard" timestamp. (Although you might want to use checkdate for validation purposes.)

Getting back to your specific questions:

1. strotime will fail if a timestamp is passed.

2. As an interim measure you could write a function that checked if the provided value was a 10 digit integer, and presume that it was a native timestamp if it was. (if(is_int($value) && strlen($value) == 10)...)

3. It's a bit of a mess by the sounds of things, so as a reasonably short term goal you should probably try and standardise usage both in MySQL and PHP.

Let mySQL handle it when you query the timestamp.

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

The query will retrieve the timestamp formatted as you need it saving you from having to do any processing the string.

But I can't run strtotime() on a timestamp; in the test I ran, if gave me 12/31/1969.

12/31/1969 is the start of the UNIX epoch, 1-1-1970, with a timezone offset.

Running strtotime() on a timestamp will return 0, which winds up as 1-1-1970 UTC when you run it through date(). Checking for a strtotime result of 0 should do it.

In short, prepend an '@' symbol to your timestamp returned from your database and you can use it in the same way as normal timestamp. This solution is suggested as a means of diverging as minimally as possible from the techniques and approaches which the questioner is familiar with (for minimal effort/confusion).

your db returns a version of a timestamp which is a string, whereas a PHP timestamp is a timestamp object.

with the '@' trick, you can code consistently. it doesn't matter whether you're processing a timestamp object from PHP or a string returned by MYSQL.

$ts1 = time();       //a PHP timestamp object
$ts2 = '1628100056'; //a string, like one coming back from a timestamp field in a MYSQL DB

//see here how they are both treated the same...
$date1 = date('Y-m-d',strtotime('@'.$ts1));
$date2 = date('Y-m-d',strtotime('@'.$ts2));

echo "from object: ".$date1;
echo "<BR>";
echo "from string: ".$date2;

the timestamp from the DB should be retrieved from the timestamp field thus:

SELECT UNIX_TIMESTAMP(my_timestamp_field) FROM my_table;