Feature idea - a plugin for relative dates

[nrdvana]

First off, I don't have any firm designs here, just some ideas that need hashed out.

I've always wanted to be able to write relative dates (especially relative to "now") in a database-agnostic manner. In most cases, it's possible to do the date math first, then specify an absolute date in the query:

$rs->where(event_date => { '<', DateTime->now->subtract(minutes => 10) });

('where' is a handy resultset method I add to all my projects equivalent to search_rs({ ... }))

however, in a few cases I'm passing that resultset to some object which might be long-lived and re-run various queries based on it, and for that, I need the time window to slide. I end up doing things like

$rs->where(event_date => \" < ( now() - interval '10 minutes' )");

but of course each engine has a different syntax.

I'd like it if there was a notation for requesting generic date math, AND if that had the ability to inspect the type of the column it was being applied to to choose the appropriate type of date, such as datetime, date, datetime with timezone, timestamp, or even consideration of columns with a declared time_zone.

$rs->where(event_date => { '<', { -date_add => [ '-now', { minutes => 10 } ] } });

It might also be fun to have some exported helper functions to make it more readable:

use SQL::Abstract::Plugin::RelDate 'SQLNOW', 'sqldate_add', 'sqldate_trunc';
$rs->where(event_date => { '<', sqldate_add(SQLNOW, minutes => 10) })

[rabbiveesh]

this sounds good, i'll try looking into the right way to detect the DB engine (this is probably simple, just haven't done it yet).

with regards to detecting the correct type: i'm not sure where we'd put handling for that - the actual SQLA object doesn't have a copy of the schema handy, so it can't quite introspect the schema def itself. Do you have a particular case where this inspection is necessary? Understanding the use-case will probably help me land on the right solution

[nrdvana]

So before I start, yes Postgres solves all these problems by using a column type that includes the timezone. But for MySQL and SQL Server you have to decide ahead of time whether the column is a UTC or local time zone, and (ideally) declare it in the DBIC schema. SQLite tries to dodge the problem by claiming that all datetime columns are UTC, but I still run into cases where I want/need to store local times into SQLite, usually for unit tests of modules that I want to run independent from a server.

So, for a toy example, lets consider a table with a column in a customer's local time (legacy code) and then I add columns which I want to be UTC, for sanity.

table 'event_calendar';
...
col 'event_start' => datetime('America/New_York');
col 'created'     => datetime('UTC');
col 'updated'     => datetime('UTC');

I don't want to change the timezone of event_start because it's referenced by a bunch of legacy code, and I don't want to use local time for my new timestamps because I don't want the hassle of daylight savings messing up my synchronization code that relies on timestamps.

I can either try to remember the time zone of each column I'm comparing on, or do something like:

   $schema->resultset('EventCalendar')->search_rs({
     created => { '>=', $schema->format_datetime($date, 'EventCalendar.created') }
   })

where format_datetime is a function I added that looks up that column in DBIC to find its timezone, and then calls the appropriate ->set_time_zone on the DateTime object, then formats it as a string for the query. For a very long time I've wanted to make something automatic so that I can just pass the date objects to DBIC and let it sort things out.

Further complicating things, if the thing being passed in is a SQL "now" function, the column timezone also makes a difference:

• SQL Server: GETDATE() or GETUTCDATE()
• MySQL: CURRENT_TIMESTAMP or UTC_TIMESTAMP()
• SQLite: datetime('now','localtime') or CURRENT_TIMESTAMP

(bucking the trend, SQLite CURRENT_TIMESTAMP is always UTC and you have to convert it to local time with a function, where every other engine gives you local time with CURRENT_TIMESTAMP)

I have handled this a few times with code like

  $schema->resultset('MessageQueue')->search_rs({
    ready_ts => { '<', $schema->sql_now_syntax('MessageQueue.ready_ts') }
  });

and then that function takes into consideration the timezone of the column, the precision of the column (some engines throw errors if you try storing a high-precision datetime into a lower precision column) and the current connection type. I've been meaning to make a CPAN module from it but I never gave it full coverage of all the cases, and besides it's a really awkward API.

I don't know if it's possible to solve this without changes to SQLA or DBIC, but if its possible, a module like yours that aims to integrate SQLA with DBIC seems like the place it could happen.

[rabbiveesh]

1st off - I'm a PG guy, so I didn't realize this was such a painful thing.

Is it possible for you to submit a PR with your prior art added to the ideas dir, and we can iterate on it from there? If you have even partial coverage of the cases, that's already more than the 'I use PG lol@mysql' that I have.

[rabbiveesh]

Sorry for the delay here, but doe https://metacpan.org/pod/DBIx::Class::Helper::ResultSet::DateMethods1 cover what you're looking for? b/c as pointed out you need DBIC knowledge, so it would make more sense to live on the RS level rather than the SQLA level

[nrdvana]

The DateMethods helper does solve a lot of date problems, but I don't think it has any handling for looking up the timezone from the column metadata.

Using the resultset object to generate appropriate SQLA structures does work as a general approach. I just wish it could go the other way, where the SQLA structure declares an intent and then details get resolved by DBIC in the process of building the SQL to match the current connection. It would be less bug-prone that way.