Re: RFC:: DateTime::Span::Common
2009/4/8 Rick Measham ri...@isite.net.au: Sounds like a job for DateTime::Format::MySQL .. $span = DateTime::Span-from_datetimes( start = $dt1, end = $dt2 ); print DateTime::Format::MySQL-format_span( $span, 'colname' ); # colname = '2009-04-08 23:22:00' AND colname = '2009-04-09 23:22:00' [...] And could be extended to SpanSets too: $spanset = DateTime::SpanSet-from_spans( spans = [ $dt_span, $dt_span ] ); # (colname = '2009-04-08 23:22:00' AND colname '2009-04-09 23:22:00') OR (colname = '2009-05-08 23:22:00' AND colname '2009-05-09 23:22:00') (Of course, this couldn't be expected to work on infinite or even large spansets) Good idea! Please use 'format_period' instead of 'format_span' - format_period() is used by DateTime::Format::ICal API. format_recurrence() is doable too. DateTime::Event::Recurrence stores some hints that make stringification possible in several cases. - Flavio S. Glock
Re: RFC:: DateTime::Span::Common
I'm currently developing somethign that will help all of the Formats standardize, take a look at expanding it with the range stuff. DateTimeX::Format -- Evan Carroll System Lord of the Internets
Re: RFC:: DateTime::Span::Common
Terrence Brannon wrote: 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. BETWEEN, then, is not the right way to express an open or half-open interval. If you're going to add anything to DT::Span, add a method that generates the correct SQL expression, taking account of the openness of each end of the interval. -zefram
Re: RFC:: DateTime::Span::Common
Zefram wrote: Terrence Brannon wrote: 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. BETWEEN, then, is not the right way to express an open or half-open interval. If you're going to add anything to DT::Span, add a method that generates the correct SQL expression, taking account of the openness of each end of the interval. -zefram Sounds like a job for DateTime::Format::MySQL .. $span = DateTime::Span-from_datetimes( start = $dt1, end = $dt2 ); print DateTime::Format::MySQL-format_span( $span, 'colname' ); # colname = '2009-04-08 23:22:00' AND colname = '2009-04-09 23:22:00' $span = DateTime::Span-from_datetimes( start = $dt1, before = $dt2 ); # colname = '2009-04-08 23:22:00' AND colname '2009-04-09 23:22:00' $span = DateTime::Span-from_datetimes( after = $dt1, before = $dt2 ); # colname '2009-04-08 23:22:00' AND colname '2009-04-09 23:22:00' $span = DateTime::Span-from_datetimes( start = $dt1 ); # colname = '2009-04-08 23:22:00' And could be extended to SpanSets too: $spanset = DateTime::SpanSet-from_spans( spans = [ $dt_span, $dt_span ] ); # (colname = '2009-04-08 23:22:00' AND colname '2009-04-09 23:22:00') OR (colname = '2009-05-08 23:22:00' AND colname '2009-05-09 23:22:00') (Of course, this couldn't be expected to work on infinite or even large spansets) Just my $0.02. Cheers! Rick Measham -- Message protected for iSite by MailGuard: e-mail anti-virus, anti-spam and content filtering. http://www.mailguard.com.au
RFC:: DateTime::Span::Common
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.
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.
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.
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);
Re: RFC:: DateTime::Span::Common
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);
Hm
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
Re: RFC:: DateTime::Span::Common
On Tue, 7 Apr 2009, Terrence Brannon wrote: 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. Unfortunately, BETWEEN is kind of broken. What people generally want is: $x = Column $y But unfortunately BETWEEN gives you: $x = Column = $y Your solution, to subtract from $y, is not a good idea, since it's extra code which could break, and the math confuses the issue, which is that you really meant to write the first comparison. So just suck it up and write: WHERE col = ? AND col ? -dave /* http://VegGuide.org http://blog.urth.org Your guide to all that's veg House Absolute(ly Pointless) */
Re: RFC:: DateTime::Span::Common
Dave Rolsky wrote: So just suck it up and write: WHERE col = ? AND col ?
