Manipulating Dates with PHP

lazylinepainter

Hi all,

This is probably going to come real easy to the majority but i need help anyways,

I would like to create a mySQL query that retrieves CD album data via their release date. The query should retrieve all albums but feed the ones with release dates only two weeks old or less into a "new release" array, and those others into another.
This is to seperate new releases from archived data.

I am trying to make the content as automated as possible so that when the release date of an album is two weeks or a month old, it will automatically be archived.

I have been racking my brains with a logical way to do this and stumbled almost instantly by not knowing how to subtract dates (i.e. find out the difference in days from current date) or even getting the dates right in the first place(I'm using DATE type for mySQL field and its stored as YYYY-DD-MM, it'd be easier if it was DD-MM-YYYY tho my text tells me the other format is native to mySQL)

Any help would be greatly appreciated, this is my first big PHP project and would like to see it through!

Thanks
Stuart 🙂

dcooper

You wont find a better explanation than at

www.php.net/date

devinemke

i don't know your exact data structure nor naming conventions but here's a simple example:

$result = mysql_query('SELECT * FROM albums');
$two_weeks_ago = date('Y-m-d', time() - 1209600);
while ($row = mysql_fetch_assoc($result))
{
	if ($row['date'] >= $two_weeks_ago) {$new_releases[] = $row;}
	else {$archives[] = $row;}
}

you now have 2 multi-dimensional arrays: $new_releases and $archives

FMB

Originally posted by devinemke
i don't know your exact data structure nor naming conventions but here's a simple example:
$result = mysql_query('SELECT * FROM albums');
$two_weeks_ago = date('Y-m-d', time() - 1209600);
while ($row = mysql_fetch_assoc($result))
{
	if ($row['date'] >= $two_weeks_ago) {$new_releases[] = $row;}
	else {$archives[] = $row;}
}
you now have 2 multi-dimensional arrays: $new_releases and $archives [/B]

for the example sake, OK, but I would do this with two queries, mysql date functions (http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html) and a mysql_fetch_array.

😉

devinemke

Originally posted by FMB
for the example sake, OK, but I would do this with two queries...

the original question seemed to suggest that lazylinepainter wanted to pull all of the records from the table (then sort out certains records depending on date). if that's the case, i say why hit the database twice, just do a single query and use PHP to do the sorting (as in my exmaple).

PHPdev

I did not notice if your database was already built or populated... so I will throw this out. I use the Unix format for storring dates as an inter of the number of seconds since the epoch. Meaning all I need it an field called "relase_date int(11)" and then use the time() function and some addition and subtraction to get the dates I need.

FYI there are 86,400 seconds in a day, so if I want two weeks ago, i just do (time - (13 * 86400)), and then the date() function to dispaly any "integer date" on the page.

Just another thought if you stuff is not already built.

PHPDev

Weedpacket

Originally posted by PHPdev
FYI there are 86,400 seconds in a day....

Except when there aren't, which is why I prefer strtotime('-2 weeks') if I want the time two weeks ago.

PHPdev

The length of the mean solar day has increased by roughly 2 milliseconds since it was exactly 86,400 seconds of atomic time about 1.79 centuries ago (i.e. the 179 year difference between 1999 and 1820). That is, the length of the mean solar day is at present about 86,400.002 seconds instead of exactly 86,400 seconds. Over the course of one year, the difference accumulates to almost one second, which is compensated by the insertion of a leap second into the scale of UTC with a current regularity of a little less than once per year. Other factors also affect the Earth, some in unpredictable ways, so that it is necessary to monitor the Earth's rotation continuously.

Credit: http://tycho.usno.navy.mil/leapsec.html

I may be crazy but if my calculations are off by 1 second in the next 500 years, I think that will be acceptable. Plus Unix time does not take into account Miliseconds.

Just a thought

PHPdev

devinemke

perhaps Weedpacket was refering to a 2 week period that straddles daylight savings time in which case calculating the number of seconds would still be wrong by 1 hour.

Weedpacket

Originally posted by devinemke
perhaps Weedpacket was refering to a 2 week period that straddles daylight savings time in which case calculating the number of seconds would still be wrong by 1 hour.

Precisely:

php -r "echo strtotime('2004-10-14')-strtotime('2004-10-01');"
1119600

php -r "echo 13*86400;"
1123200

php -r "echo 1119600-1123200;"
-3600

Your results may vary.

PHPdev

if that be the case then I must ask this... What about countries around the world that do not observe DST?

I thought that the unix timestamp is the number of seconds since January 1 1970 00:00:00 GMT, so since the timestamp is from GMT, and they observe DST do all Unix systems around the world exhibit DST?

Seems to me that the number of seconds from a date should be X.

Thanks

PHPdev

Weedpacket

I thought that the unix timestamp is the number of seconds since January 1 1970 00:00:00 GMT

It does, but I wasn't using GMT:

php -r "echo strtotime('2004-10-14 GMT')-strtotime('2004-10-01 GMT');"
1123200

so since the timestamp is from GMT, and they observe DST

No, GMT does not do Daylight Saving. There would be no purpose in it (and "they" can do what they like to their clocks without changing that).

Seems to me that the number of seconds from a date should be X.

Which it is - for some value of X.

if that be the case then I must ask this... What about countries around the world that do not observe DST?

Your results may vary.