On Tue, Apr 7, 2009 at 4:02 PM, Terrence Brannon <scheme...@gmail.com> wrote:
> On Tue, Mar 31, 2009 at 2:17 PM, Zefram <zef...@fysh.org> wrote:
>> Terrence Brannon wrote:
>>>Intuitively, it would seem that specifying the 'before' of a datetime
>>>span using the end option of the ->from_datetimes() constructor would
>>>yield a range that is 1 second (1 nanosecond?) earlier than the actual
>>>date supplied.
>>
>> I would not find that intuitive. Your 1 second or 1 nanosecond would
>> be a completely arbitrary mutation of the supplied data.
>
Neither would I.
> Yes, I would determine that aribtrary 1 second or nanosecond based on
> the data in my database tables. a MySQL BETWEEN clause includes its
> endpoints.
>
That is correct.
> If I want all data for a certain date, my BETWEEN clause must be from
> 00:00::00 to 23:59:59 assuming a datetime column.
>
That is not correct, UNLESS you ALSO want to support arbitrary
intervals that include part days. Otherwise, if you know the date for
which you want your data, you can cast the datetime to a date and test
for equality against the date you require. I use that trick all the
time, even when I need to take time zones into account (provided MySQL
knows about the time zone in question). And it works fine for
arbitrary periods as long as the temporal granularity is a day.
> Using the start and end dates supplied by Datetime::Span would lead to
> overlapping SQL query results on successive days, hence the following
> module:
>
> use DateTime::Span::Common;
> # ideal for DATETIME columns
> my $x = DateTime::Span::Common->new(shave => [seconds => 1]);
>
> # not sure what this would be practical for
> my $y = DateTime::Span::Common->new(shave => [nanoseconds => 1]);
>
> my @dto = $x->today;
> �...@dto = $x->yesterday;
> �...@dto = $x->this_week;
> �...@dto = $x->last_week;
> �...@dto = $x->this_month;
> �...@dto = $x->last_month;
> �...@dto = $x->year_to_date;
> �...@dto = $x->last_year;
>
> my $sth = $dbh->prepare('SELECT * FROM table WHERE order_date
> BETWEEN ? AND ?');
> $sth->execute(@dto);
>
Hmmmmmmmmm
Interesting. I will have to look into DateTime::Span, especially how
it works with timezones. That is a neat construct.
When I have had to use perl to compute dates and times, I have used
either Date::manip or datetime, for calculations involving time zones
and taking into account that our different offices are in different
time zones, but I hadn't looked at DateTime::Span yet.
Surely, if you ask it for time stamps that include all valid times for
a given day (or a set of such intervals for a series of days), it will
give you times from 00:00:00 to 23:59:59 (as a closed interval). The
only way I can see it giving you overlapping intervals is if it gives
you a half open interval (00:00:00 on one day and 00:00:00 on the next
day): and that doesn't strike me as intuitive (unless it is documented
as behaving like that: in which case I would derive a new class from
it to give me a closed interval I can use).
Just a thought.
Ted
