1DBIx::Class::Helper::ReUssuelrtSCeotn:t:rDDiBabItuxet:Me:edCtlhPaoesdrssl:1:(DH3oe)clupmeern:t:aRteisounltSet::DateMethods1(3)
2
3
4

NAME

6       DBIx::Class::Helper::ResultSet::DateMethods1 - Work with dates in your
7       RDBMS nicely
8

SYNOPSIS

10        package MySchema::ResultSet::Bar;
11
12        use strict;
13        use warnings;
14
15        use parent 'DBIx::Class::ResultSet';
16
17        __PACKAGE__->load_components('Helper::ResultSet::DateMethods1');
18
19        # in code using resultset
20
21        # 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
34        # 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
37        # 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

DESCRIPTION

41       See "NOTE" in DBIx::Class::Helper::ResultSet for a nice way to apply it
42       to your entire schema.
43
44       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
49       This package has a few types of methods.
50
51       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
56       The date helper
57           There is only one: "utc".  Makes searching with dates a little
58           easier.
59
60       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

TYPES

66       Because these methods are so limited in scope they can be a bit more
67       smart than typical "SQL::Abstract" trees.
68
69       There are "smart types" that this package supports.
70
71       •   vanilla scalars (1, "2012-12-12 12:12:12")
72
73           bound directly as untyped values
74
75       •   hashrefs with an "-ident" ("{ -ident => '.foo' }")
76
77           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
82       •   DateTime objects
83
84           "DateTime" objects work as if they were passed to "utc".
85
86       •   "ScalarRef" ("\'NOW()'")
87
88           As usual in "DBIx::Class", "ScalarRef"'s will be flattened into
89           regular SQL.
90
91       •   "ArrayRefRef" ("\["SELECT foo FROM bar WHERE id = ?", [{}, 1]]")
92
93           As usual in "DBIx::Class", "ArrayRefRef"'s will be flattened into
94           SQL with bound values.
95
96       Anything not mentioned in the above list will explode, one way or
97       another.
98

IMPLEMENTATION

100       The exact details for the functions your database engine provides.
101
102       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
106   "SQL Server"
107       •   "utc_now" - GETUTCDATE <http://msdn.microsoft.com/en-
108           us/library/ms178635.aspx>
109
110       •   "dt_SQL_pluck" - DATEPART <http://msdn.microsoft.com/en-
111           us/library/ms174420.aspx>
112
113           Supported units
114
115           •   year
116
117           •   quarter
118
119           •   month
120
121           •   day_of_year
122
123           •   day_of_month
124
125           •   week
126
127           •   day_of_week
128
129           •   hour
130
131           •   minute
132
133           •   second
134
135           •   millisecond
136
137           •   nanosecond ⚠
138
139           •   non_iso_day_of_week
140
141               SQL Server offers both "ISO_WEEK" and "weekday".  For interop
142               reasons "weekday" uses the "ISO_WEEK" version.
143
144           •   timezone_as_minutes ⚠
145
146       •   "dt_SQL_add" - DATEADD <http://msdn.microsoft.com/en-
147           us/library/ms186819.aspx>
148
149           Supported units
150
151           •   year
152
153           •   quarter
154
155           •   month
156
157           •   day
158
159           •   week
160
161           •   hour
162
163           •   minute
164
165           •   second
166
167           •   millisecond
168
169           •   nanosecond ⚠
170
171           •   iso_day_of_week
172
173           •   timezone_as_minutes ⚠
174
175   "SQLite"
176       •   "utc_now" - DATETIME('now')
177           <https://www.sqlite.org/lang_datefunc.html>
178
179       •   "dt_SQL_pluck" - STRFTIME
180           <https://www.sqlite.org/lang_datefunc.html>
181
182           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
187           Available units
188
189           •   month
190
191           •   day_of_month
192
193           •   year
194
195           •   hour
196
197           •   day_of_year
198
199           •   minute
200
201           •   second
202
203           •   day_of_week
204
205           •   week
206
207           •   julian_day ⚠
208
209           •   seconds_since_epoch
210
211           •   fractional_seconds ⚠
212
213       •   "dt_SQL_add" - DATETIME <https://www.sqlite.org/lang_datefunc.html>
214
215           Available units
216
217           •   day
218
219           •   hour
220
221           •   minute
222
223           •   second
224
225           •   month
226
227           •   year
228
229   "PostgreSQL"
230       •   "utc_now" - CURRENT_TIMESTAMP
231           <http://www.postgresql.org/docs/current/static/functions-
232           datetime.html#FUNCTIONS-DATETIME-CURRENT>
233
234       •   "dt_SQL_pluck" - date_part
235           <http://www.postgresql.org/docs/current/static/functions-
236           datetime.html#FUNCTIONS-DATETIME-EXTRACT>
237
238           Available units
239
240           •   century ⚠
241
242           •   decade ⚠
243
244           •   day_of_month
245
246           •   day_of_week
247
248           •   day_of_year
249
250           •   seconds_since_epoch
251
252           •   hour
253
254           •   iso_day_of_week
255
256           •   iso_year
257
258           •   microsecond
259
260           •   millenium ⚠
261
262           •   millisecond
263
264           •   minute
265
266           •   month
267
268           •   quarter
269
270           •   second
271
272           •   timezone ⚠
273
274           •   timezone_hour ⚠
275
276           •   timezone_minute ⚠
277
278           •   week
279
280           •   year
281
282       •   "dt_SQL_add" - Addition and interval
283           <http://www.postgresql.org/docs/current/static/functions-
284           datetime.html#OPERATORS-DATETIME-TABLE>
285
286           To be clear, it ends up looking like: "("some_column" + 5 *
287           interval '1 minute')"
288
289           Available units
290
291           •   century ⚠
292
293           •   decade ⚠
294
295           •   day
296
297           •   hour
298
299           •   microsecond ⚠
300
301           •   millisecond
302
303           •   minute
304
305           •   month
306
307           •   second
308
309           •   week
310
311           •   year
312
313   "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
318       •   "dt_SQL_pluck" - EXTRACT
319           <https://dev.mysql.com/doc/refman/5.1/en/date-and-time-
320           functions.html#function_extract>
321
322           Available units
323
324           •   microsecond
325
326           •   second
327
328           •   minute
329
330           •   hour
331
332           •   day_of_month
333
334           •   week
335
336           •   month
337
338           •   quarter
339
340           •   year
341
342           •   second_microsecond ⚠
343
344           •   minute_microsecond ⚠
345
346           •   minute_second ⚠
347
348           •   hour_microsecond ⚠
349
350           •   hour_second ⚠
351
352           •   hour_minute ⚠
353
354           •   day_microsecond ⚠
355
356           •   day_second ⚠
357
358           •   day_minute ⚠
359
360           •   day_hour ⚠
361
362           •   year_month ⚠
363
364       •   "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
368           Available units
369
370           •   microsecond
371
372           •   second
373
374           •   minute
375
376           •   hour
377
378           •   day
379
380           •   week
381
382           •   month
383
384           •   quarter
385
386           •   year
387
388   "Oracle"
389       •   "utc_now" - sys_extract_utc(SYSTIMESTAMP)
390           <http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions167.htm>
391
392       •   "dt_SQL_pluck" - EXTRACT
393
394           Available units
395
396           •   second
397
398           •   minute
399
400           •   hour
401
402           •   day_of_month
403
404           •   month
405
406           •   year
407
408       •   "dt_SQL_add" - Addition and NUMTODSINTERVAL
409           <http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions103.htm>
410
411           To be clear, it ends up looking like: "("some_column" +
412           NUMTODSINTERVAL(4, 'MINUTE')"
413
414           Available units
415
416           •   second
417
418           •   minute
419
420           •   hour
421
422           •   day
423

CONTRIBUTORS

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
429       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

WHENCE dt_SQL_diff?

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

METHODS

445   utc
446        $rs->search({
447          'some_date' => $rs->utc($datetime),
448        })->all
449
450       Takes a DateTime object, updates the "time_zone" to "UTC", and formats
451       it according to whatever database engine you are using.
452
453       Dies if you pass it a date with a "floating time_zone".
454
455   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
459   dt_before
460        $rs->dt_before({ -ident => '.start' }, { -ident => '.end' })->all
461
462       Takes two values, each an expression of "TYPES".
463
464   dt_on_or_before
465        $rs->dt_on_or_before({ -ident => '.start' }, DateTime->now)->all
466
467       Takes two values, each an expression of "TYPES".
468
469   dt_on_or_after
470        $rs->dt_on_or_after(DateTime->now, { ident => '.end' })->all
471
472       Takes two values, each an expression of "TYPES".
473
474   dt_after
475        $rs->dt_after({ ident => '.end' }, $rs->get_column('datecol')->as_query)->all
476
477       Takes two values, each an expression of "TYPES".
478
479   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
486       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
490   dt_SQL_subtract
491       Same as dt_SQL_add, but subtracts the amount.
492
493       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
497       This method was implemented by Thomas Klausner <https://domm.plix.at>
498       and sponsored by Ctrl O <https://www.ctrlo.com/>.
499
500   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
510       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

AUTHOR

515       Arthur Axel "fREW" Schmidt <frioux+cpan@gmail.com>
516

518       This software is copyright (c) 2020 by Arthur Axel "fREW" Schmidt.
519
520       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.
522
523
524
525perl v5.32.1                   DBI2x0:2:1C-l0a1s-s2:7:Helper::ResultSet::DateMethods1(3)
Impressum