Convert MySQL DATETIME (UNIX timestamp) to HTTP Cache-Control?

Forum Moderators: coopster

Message Too Old, No Replies

Convert MySQL DATETIME (UNIX timestamp) to HTTP Cache-Control?

Taking time zones in to consideration as well.

JAB Creations

9:14 am on Jul 30, 2010 (gmt 0)

After reading a featured article about the updated W3C validator I took the latest build of my site and got the score to 100% on the mobile checker though I wasn't happy with some of the warnings including one referencing Cache-Control being listed. Sure enough I realized that since I was pulling content from a database I'd have to go out of my way to set the last modified header. So I use MySQL's NOW() to store datetime stamps as the datetime type.

So I'm trying to determine the most efficient way to convert a Unix timestamp (e.g. 2010-04-04 04:04:04) to a HTTP Cache-Control accepted format (e.g. Sun, 04 Jul 2010 04:04:04 GMT) along with adjustment for varying time zones.

Right now I'm still working with chicken scratch as there doesn't seem to be much if anything about this topic specifically. I'd also like to avoid using the Unix Epoch if at all possible. My host is running PHP 5.2.x on my live though I'm running 5.3.x locally.

Thoughts please?

- John

coopster

1:12 pm on Jul 30, 2010 (gmt 0)

gmdate [php.net](DATE_RFC1123);

JAB Creations

1:40 pm on Jul 30, 2010 (gmt 0)

Thanks coopster, I've been testing a small scratchpad of PHP code. This is generally all that I have that has worked since this morning while I've done a lot of reading on php.net and whatever I can find that seems even vaguely relevant on search engines...

The main issue I think I'm having is that I'm not seeing a way to convert from a Unix timestamp as using MySQL's NOW() to create a datetime value is the source datetime stamp I'll be working with...unless there is a more efficient solution?

- John

<?php
$date1 = gmdate('D, d M Y H:i:s');
$date2 = date('D, d M Y H:i:s');

echo '<div>'.$date1.'</div>';
echo '<div>'.$date2.'</div> ';

//date_default_timezone_set('Europe/London');
?>

coopster

4:48 pm on Jul 30, 2010 (gmt 0)

OK, I missed this the first time ...

to convert a Unix timestamp (e.g. 2010-04-04 04:04:04)

That's not a UNIX timestamp, that is an ISO formatted timestamp. The function I showed an example of uses the UNIX timestamp, which is seconds since the epoch.

If you are storing the DATETIME value as yyyy-mm-dd hh:mm:ss in MySQL then you can either convert it during your query using the MySQL UNIX_TIMESTAMP [dev.mysql.com] function or afterward with a PHP function. Since it's coming from the DB, I would let it be the workhorse in this case. Simply pass the properly formatted query result value as the second parameter to the gmdate() as I showed earlier.

JAB Creations

8:01 am on Jul 31, 2010 (gmt 0)

Awesome, I think I have this working though not yet implemented and I do have some future concerns though let me explain how I have this working in proof of concept step by step...

First I use MySQL's NOW() to create a datetime stamp that you said was ISO format (e.g. 2010-04-04 04:04:04).

Then when reading from the database I have MySQL convert the ISO dates to the Unix Epoch...

SELECT UNIX_TIMESTAMP(date) FROM example_table

Finally to reformat the Unix epoch date in to GMT time I simply do the following...

<?php
$date3 = gmdate('D, d M Y H:i:s','1270215235');
echo '<div>'.$date3.'</div>';
?>

I do have some general concerns about 32 bit integers being handled by PHP in this regards hence a new separate thread I started though I don't foresee encountering that issue too soon and there won't be any way for users to manipulate this (this will be used for the last modified date that is accessible only to me on my site's new CMS) though it is something I want to address sooner rather then later.

Any way the main issue seems to be resolved now to the best of my current understanding and thanks for clarifying that I was actually referencing an ISO datetime stamp. :)

- John