Re: [PHP] Problems processing UNIX timestamps in events directory

On Tue, February 20, 2007 8:25 am, Dave Goodchild wrote:
> I am building an events directory and when users search, they provide
> a date
> range ie Feb 16 2007 to March 12 2007 as one of the options for
> searching.
> The system then creates an array of timestamps from this and then

Why would you create a range, which could potentially be quite a large
set, instead of using the SQL "BETWEEN" operator, or even just using:

WHERE whatdate >= '$start_date'
AND whatdate <= '$end_date'

It just seems to me like you are creating a giant JOIN for no real
purpose, which is going to end up slogging your database as soon as
you extend the date range out more than a few weeks...



> polls the
> database for all events whose lifetimes (start to end data) intersect
> with
> or contain the user date ranges. The system then checks how many of
> those
> dates map onto the dates provided by the user and returns an array of
> events
> on specific dates, all by comparing timestamps.
>
> The system is working like a dream apart from the following. If the
> user
> selects a start date prior to March 26 2007 or after October 29th 2007
> all
> is well. If they specify a start date after March 26 the events are
> not
> being pulled in.
>
> I have converted the user-friendly date output to timestamps to check
> and
> sure enough, when the user selects a start date before March 26 2007,
> March
> 26 2007 is output as:
>
> 1174863600
>
> ...after that it becomes:
>
> 1174860000
>
> ...a difference of 3600
>
> Is this anything to do with leap seconds or any other clock drift
> phenomenon
> anyone has seen before? Much hair being torn out at present!

This probably is a daylight savings time issue...

But if you don't care about the hour/minute/second, you probably
should not be messing with Unix time-stamp in the first place...

--
Some people have a "gift" link here.
Know what I want?
I want you to buy a CD from some starving artist.
http://cdbaby.com/browse/from/lynch
Yeah, I get a buck. So?

I AM using the logic you and Jochem suggest in the query, I am not
performing any joins. I query for certain criteria re the start and end
dates held in the database, and once I have my set I then work with it
dynamically as I need to know, based on the event frequency, how many events
from that range fall on dates within the user range.

Thanks all for your input - you're all correct, I don't really need to use
timestamps as I am only interested in dates so will be reworking the app to
use dates only. I still need to generate sequences of dates and really don't
want to deal with DST, so my question is: can I add days to dates without
converting to timestamp and adding 86400 for example. If I can it will make
things much simpler. I am talking about doing this dynamically with php
rather than at database level. Many thanks in advance, I will also start
googling...


--
http://www.web-buddha.co.uk

2007. 02. 21, szerda keltezéssel 09.33-kor Dave Goodchild ezt Ã*rta:
> Thanks all for your input - you're all correct, I don't really need to use
> timestamps as I am only interested in dates so will be reworking the app to
> use dates only. I still need to generate sequences of dates and really don't
> want to deal with DST, so my question is: can I add days to dates without
> converting to timestamp and adding 86400 for example. If I can it will make
> things much simpler. I am talking about doing this dynamically with php
> rather than at database level. Many thanks in advance, I will also start
> googling...
>
>

of course you can

use something like this:

$newdate = date("Y-m-d", mktime(0, 0, 0, $orig_month, $orig_day + 1,
$orig_year));

greets
Zoltán Németh