1DBIx::Class::Helper::ReUssuelrtSCeotn:t:rDDiBabItuxet:Me:edCtlhPaoesdrssl:1:(DH3oe)clupmeern:t:aRteisounltSet::DateMethods1(3)
2
6 DBIx::Class::Helper::ResultSet::DateMethods1 - Work with dates in your
7 RDBMS nicely
8
10 package MySchema::ResultSet::Bar;
11 use strict;
13 use warnings;
14 use parent 'DBIx::Class::ResultSet';
16 __PACKAGE__->load_components('Helper::ResultSet::DateMethods1');
18 # in code using resultset
20 # get count per year/month
22 $rs->search(undef, {
23 columns => {
24 count => '*',
25 year => $rs->dt_SQL_pluck({ -ident => '.start' }, 'year'),
26 month => $rs->dt_SQL_pluck({ -ident => '.start' }, 'month'),
27 },
28 group_by => [
29 $rs->dt_SQL_pluck({ -ident => '.start' }, 'year'),
30 $rs->dt_SQL_pluck({ -ident => '.start' }, 'month'),
31 ],
32 });
33 # mysql
35 (SELECT `me`.*, EXTRACT(MONTH FROM `me`.`start`), EXTRACT(YEAR FROM `me`.`start`) FROM `HasDateOps` `me` GROUP BY EXTRACT(YEAR FROM `me`.`start`), EXTRACT(MONTH FROM `me`.`start`))
36 # SQLite
38 (SELECT "me".*, STRFTIME('%m', "me"."start"), STRFTIME('%Y', "me"."start") FROM "HasDateOps" "me" GROUP BY STRFTIME('%Y', "me"."start"), STRFTIME('%m', "me"."start"))
39
41 See "NOTE" in DBIx::Class::Helper::ResultSet for a nice way to apply it
42 to your entire schema.
43 This ResultSet component gives the user tools to do mostly portable
45 date manipulation in the database. Before embarking on a cross
46 database project, take a look at "IMPLEMENTATION" to see what might
47 break on switching databases.
48 This package has a few types of methods.
50 Search Shortcuts
52 These, like typical ResultSet methods, return another ResultSet.
53 See "dt_before", "dt_on_or_before", "dt_on_or_after", and
54 "dt_after".
55 The date helper
57 There is only one: "utc". Makes searching with dates a little
58 easier.
59 SQL generators
61 These help generate more complex queries. The can be used in many
62 different parts of "search" in DBIx::Class::ResultSet. See
63 "utc_now", "dt_SQL_pluck", and "dt_SQL_add".
64
66 Because these methods are so limited in scope they can be a bit more
67 smart than typical "SQL::Abstract" trees.
68 There are "smart types" that this package supports.
70 · vanilla scalars (1, "2012-12-12 12:12:12")
72 bound directly as untyped values
74 · hashrefs with an "-ident" ("{ -ident => '.foo' }")
76 As usual this gets flattened into a column. The one special
78 feature in this module is that columns starting with a dot will
79 automatically be prefixed with "current_source_alias" in
80 DBIx::Class::ResultSet.
81 · DateTime objects
83 "DateTime" objects work as if they were passed to "utc".
85 · "ScalarRef" ("\'NOW()'")
87 As usual in "DBIx::Class", "ScalarRef"'s will be flattened into
89 regular SQL.
90 · "ArrayRefRef" ("\["SELECT foo FROM bar WHERE id = ?", [{}, 1]]")
92 As usual in "DBIx::Class", "ArrayRefRef"'s will be flattened into
94 SQL with bound values.
95 Anything not mentioned in the above list will explode, one way or
97 another.
98
100 The exact details for the functions your database engine provides.
101 If a piece of functionality is flagged with ⚠, it means that the
103 feature in question is not portable at all, and only supported on that
104 engine.
105 "SQL Server"
107 · "utc_now" - GETUTCDATE <http://msdn.microsoft.com/en-
108 us/library/ms178635.aspx>
109 · "dt_SQL_pluck" - DATEPART <http://msdn.microsoft.com/en-
111 us/library/ms174420.aspx>
112 Supported units
114 · year
116 · quarter
118 · month
120 · day_of_year
122 · day_of_month
124 · week
126 · day_of_week
128 · hour
130 · minute
132 · second
134 · millisecond
136 · nanosecond ⚠
138 · non_iso_day_of_week
140 SQL Server offers both "ISO_WEEK" and "weekday". For interop
142 reasons "weekday" uses the "ISO_WEEK" version.
143 · timezone_as_minutes ⚠
145 · "dt_SQL_add" - DATEADD <http://msdn.microsoft.com/en-
147 us/library/ms186819.aspx>
148 Supported units
150 · year
152 · quarter
154 · month
156 · day
158 · week
160 · hour
162 · minute
164 · second
166 · millisecond
168 · nanosecond ⚠
170 · iso_day_of_week
172 · timezone_as_minutes ⚠
174 "SQLite"
176 · "utc_now" - DATETIME('now')
177 <https://www.sqlite.org/lang_datefunc.html>
178 · "dt_SQL_pluck" - STRFTIME
180 <https://www.sqlite.org/lang_datefunc.html>
181 Note: "SQLite"'s pluck implementation pads numbers with zeros,
183 because it is implemented on based on a formatting function. If
184 you want your code to work on SQLite you'll need to strip off (or
185 just numify) what you get out of the database first.
186 Available units
188 · month
190 · day_of_month
192 · year
194 · hour
196 · day_of_year
198 · minute
200 · second
202 · day_of_week
204 · week
206 · julian_day ⚠
208 · seconds_since_epoch
210 · fractional_seconds ⚠
212 · "dt_SQL_add" - DATETIME <https://www.sqlite.org/lang_datefunc.html>
214 Available units
216 · day
218 · hour
220 · minute
222 · second
224 · month
226 · year
228 "PostgreSQL"
230 · "utc_now" - CURRENT_TIMESTAMP
231 <http://www.postgresql.org/docs/current/static/functions-
232 datetime.html#FUNCTIONS-DATETIME-CURRENT>
233 · "dt_SQL_pluck" - date_part
235 <http://www.postgresql.org/docs/current/static/functions-
236 datetime.html#FUNCTIONS-DATETIME-EXTRACT>
237 Available units
239 · century ⚠
241 · decade ⚠
243 · day_of_month
245 · day_of_week
247 · day_of_year
249 · seconds_since_epoch
251 · hour
253 · iso_day_of_week
255 · iso_year
257 · microsecond
259 · millenium ⚠
261 · millisecond
263 · minute
265 · month
267 · quarter
269 · second
271 · timezone ⚠
273 · timezone_hour ⚠
275 · timezone_minute ⚠
277 · week
279 · year
281 · "dt_SQL_add" - Addition and interval
283 <http://www.postgresql.org/docs/current/static/functions-
284 datetime.html#OPERATORS-DATETIME-TABLE>
285 To be clear, it ends up looking like: "("some_column" + 5 *
287 interval '1 minute')"
288 Available units
290 · century ⚠
292 · decade ⚠
294 · day
296 · hour
298 · microsecond ⚠
300 · millisecond
302 · minute
304 · month
306 · second
308 · week
310 · year
312 "MySQL"
314 · "utc_now" - UTC_TIMESTAMP
315 <https://dev.mysql.com/doc/refman/5.1/en/date-and-time-
316 functions.html#function_utc-timestamp>
317 · "dt_SQL_pluck" - EXTRACT
319 <https://dev.mysql.com/doc/refman/5.1/en/date-and-time-
320 functions.html#function_extract>
321 Available units
323 · microsecond
325 · second
327 · minute
329 · hour
331 · day_of_month
333 · week
335 · month
337 · quarter
339 · year
341 · second_microsecond ⚠
343 · minute_microsecond ⚠
345 · minute_second ⚠
347 · hour_microsecond ⚠
349 · hour_second ⚠
351 · hour_minute ⚠
353 · day_microsecond ⚠
355 · day_second ⚠
357 · day_minute ⚠
359 · day_hour ⚠
361 · year_month ⚠
363 · "dt_SQL_add" - DATE_ADD
365 <https://dev.mysql.com/doc/refman/5.1/en/date-and-time-
366 functions.html#function_date-add>
367 Available units
369 · microsecond
371 · second
373 · minute
375 · hour
377 · day
379 · week
381 · month
383 · quarter
385 · year
387 "Oracle"
389 · "utc_now" - sys_extract_utc(SYSTIMESTAMP)
390 <http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions167.htm>
391 · "dt_SQL_pluck" - EXTRACT
393 Available units
395 · second
397 · minute
399 · hour
401 · day_of_month
403 · month
405 · year
407 · "dt_SQL_add" - Addition and NUMTODSINTERVAL
409 <http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions103.htm>
410 To be clear, it ends up looking like: "("some_column" +
412 NUMTODSINTERVAL(4, 'MINUTE')"
413 Available units
415 · second
417 · minute
419 · hour
421 · day
423
425 These people worked on the original implementation, and thus deserve
426 some credit for at least providing me a reference to implement this
427 based off of:
428 Alexander Hartmaier (abraxxa) for Oracle implementation details
430 Devin Austin (dhoss) for Pg implementation details
431 Rafael Kitover (caelum) for providing a test environment with lots of
432 DBs
433
435 The original implementation of these date helpers (originally dubbed
436 date operators) included a third operator called "diff". It existed to
437 subtract one date from another and return a duration. After using it a
438 few times and getting bitten every time, I decided to stop using it and
439 instead compare against actual dates always. If someone can come up
440 with a good use case I am interested in re-implementing "dt_SQL_diff",
441 but I worry that it will be very unportable and generally not very
442 useful.
443
445 utc
446 $rs->search({
447 'some_date' => $rs->utc($datetime),
448 })->all
449 Takes a DateTime object, updates the "time_zone" to "UTC", and formats
451 it according to whatever database engine you are using.
452 Dies if you pass it a date with a "floating time_zone".
454 utc_now
456 Returns a "ScalarRef" representing the way to get the current date and
457 time in "UTC" for whatever database engine you are using.
458 dt_before
460 $rs->dt_before({ -ident => '.start' }, { -ident => '.end' })->all
461 Takes two values, each an expression of "TYPES".
463 dt_on_or_before
465 $rs->dt_on_or_before({ -ident => '.start' }, DateTime->now)->all
466 Takes two values, each an expression of "TYPES".
468 dt_on_or_after
470 $rs->dt_on_or_after(DateTime->now, { ident => '.end' })->all
471 Takes two values, each an expression of "TYPES".
473 dt_after
475 $rs->dt_after({ ident => '.end' }, $rs->get_column('datecol')->as_query)->all
476 Takes two values, each an expression of "TYPES".
478 dt_SQL_add
480 # which ones start in 3 minutes?
481 $rs->dt_on_or_after(
482 { ident => '.start' },
483 $rs->dt_SQL_add($rs->utc_now, 'minute', 3)
484 )->all
485 Takes three arguments: a date conforming to "TYPES", a unit, and an
487 amount. The idea is to add the given unit to the datetime. See your
488 "IMPLEMENTATION" for what units are accepted.
489 dt_SQL_subtract
491 Same as dt_SQL_add, but subtracts the amount.
492 Only confirmed to work with Postgres, MySQL and SQLite. It should work
494 with Oracle and MSSQL, but due to lack of access to those DB engines
495 the implementation was done only based on docs.
496 This method was implemented by Thomas Klausner <https://domm.plix.at>
498 and sponsored by Ctrl O <https://www.ctrlo.com/>.
499 dt_SQL_pluck
501 # get count per year
502 $rs->search(undef, {
503 columns => {
504 count => '*',
505 year => $rs->dt_SQL_pluck({ -ident => '.start' }, 'year'),
506 },
507 group_by => [$rs->dt_SQL_pluck({ -ident => '.start' }, 'year')],
508 })->hri->all
509 Takes two arguments: a date conforming to "TYPES" and a unit. The idea
511 is to pluck a given unit from the datetime. See your "IMPLEMENTATION"
512 for what units are accepted.
513
515 Arthur Axel "fREW" Schmidt <frioux+cpan@gmail.com>
516
518 This software is copyright (c) 2020 by Arthur Axel "fREW" Schmidt.
519 This is free software; you can redistribute it and/or modify it under
521 the same terms as the Perl 5 programming language system itself.
522perl v5.32.0 DBI2x0:2:0C-l0a7s-s2:8:Helper::ResultSet::DateMethods1(3)