DBIx::...thods1(3pm)

✨ NAME ✨

DBIx::Class::Helper::ResultSet::DateMethods1 — bestie, this is your lil' toolkit for working with dates in your RDBMS nicely 💅

It's a 3pm page, a piece of User Contributed Perl Documentation, and honestly? It's giving "I just want EXTRACT without crying." 🎀

---

📖 SYNOPSIS 📖

package MySchema::ResultSet::Bar;

use strict;
use warnings;

use parent 'DBIx::Class::ResultSet';

__PACKAGE__->load_components('Helper::ResultSet::DateMethods1');

# in code using resultset

# get count per year/month
$rs->search(undef, {
   columns => {
      count => '*',
      year  => $rs->dt_SQL_pluck({ -ident => '.start' }, 'year'),
      month => $rs->dt_SQL_pluck({ -ident => '.start' }, 'month'),
   },
   group_by => [
      $rs->dt_SQL_pluck({ -ident => '.start' }, 'year'),
      $rs->dt_SQL_pluck({ -ident => '.start' }, 'month'),
   ],
});

# mysql
(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`))

# SQLite
(SELECT "me".*, STRFTIME('%m', "me"."start"), STRFTIME('%Y', "me"."start") FROM "HasDateOps" "me" GROUP BY STRFTIME('%Y', "me"."start"), STRFTIME('%m', "me"."start"))

Look at that. One Perl line in, two completely different SQL dialects out. The database does the translating, you just vibe. 🦋

---

💖 DESCRIPTION 💖

First things first: go read "NOTE" in DBIx::Class::Helper::ResultSet — that's the cute lil' trick for applying this component to your entire schema at once. Slay. ✨

This ResultSet component hands you the tools to do date manipulation mostly portably in the database. But before you go booking a one-way ticket on a cross-database project, please please please peek at "IMPLEMENTATION" below, because it spells out exactly what might break when you switch engines. No cap, some stuff is engine-locked and you deserve to know. 💀

This package has a few types of methods, okurrr:

🎯 Search Shortcuts

These, like typical ResultSet methods, return another ResultSet. Go look at "dt_before", "dt_on_or_before", "dt_on_or_after", and "dt_after". Chainable, adorable, iconic. 🌸

⏰ The date helper

There is only one: "utc". It just makes searching with dates a little easier. One job, does it well, main character energy. 👑

🧪 SQL generators

These help generate more complex queries. They can be used in many different parts of "search" in DBIx::Class::ResultSet. Go see "utc_now", "dt_SQL_pluck", and "dt_SQL_add". Build-a-bear but for SQL. 🧸

---

🧠 TYPES 🧠

Because these methods are so limited in scope, they can be a bit more smart than typical SQL::Abstract trees. Lowkey they're overachievers.

There are "smart types" that this package supports:

• vanilla scalars (1, "2012-12-12 12:12:12")

bound directly as untyped values

• hashrefs with an -ident ({ -ident => '.foo' })

As usual this gets flattened into a column. The one special feature in this module is that columns starting with a dot will automatically be prefixed with "current_source_alias" in DBIx::Class::ResultSet.

• DateTime objects

"DateTime" objects work as if they were passed to "utc".

• ScalarRef (\'NOW()')

As usual in "DBIx::Class", "ScalarRef"'s will be flattened into regular SQL.

• ArrayRefRef (\["SELECT foo FROM bar WHERE id = ?", [{}, 1]])

As usual in "DBIx::Class", "ArrayRefRef"'s will be flattened into SQL with bound values.

⚠️ Anything not mentioned in the above list will explode, one way or another. Not a vibe check you want to fail. 🙈

---

🛠 IMPLEMENTATION 🛠

The exact details for the functions your database engine provides. Engine-specific lore incoming. 🕵️

If a piece of functionality is flagged with ⚠, it means that the feature in question is not portable at all, and only supported on that engine. So like, don't expect it to travel. 🧳💀

🏢 "SQL Server"

• "utc_now" — GETUTCDATE http://msdn.microsoft.com/en-us/library/ms178635.aspx
• "dt_SQL_pluck" — DATEPART http://msdn.microsoft.com/en-us/library/ms174420.aspx

Supported units

• year
• quarter
• month
• day_of_year
• day_of_month
• week
• day_of_week
• hour
• minute
• second
• millisecond
• nanosecond ⚠
• non_iso_day_of_week

SQL Server offers both ISO_WEEK and weekday. For interop reasons weekday uses the ISO_WEEK version.

• timezone_as_minutes ⚠

• "dt_SQL_add" — DATEADD http://msdn.microsoft.com/en-us/library/ms186819.aspx

Supported units

• year
• quarter
• month
• day
• week
• hour
• minute
• second
• millisecond
• nanosecond ⚠
• iso_day_of_week
• timezone_as_minutes ⚠

🪶 "SQLite"

• "utc_now" — DATETIME('now') https://www.sqlite.org/lang_datefunc.html
• "dt_SQL_pluck" — STRFTIME https://www.sqlite.org/lang_datefunc.html

📌 Note: "SQLite"'s pluck implementation pads numbers with zeros, because it is implemented based on a formatting function. If you want your code to work on SQLite you'll need to strip off (or just numify) what you get out of the database first. Fr fr, don't skip this. 😭

Available units

• month
• day_of_month
• year
• hour
• day_of_year
• minute
• second
• day_of_week
• week
• julian_day ⚠
• seconds_since_epoch
• fractional_seconds ⚠

• "dt_SQL_add" — DATETIME https://www.sqlite.org/lang_datefunc.html

Available units

• day
• hour
• minute
• second
• month
• year

🐘 "PostgreSQL"

• "utc_now" — CURRENT_TIMESTAMP http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT
• "dt_SQL_pluck" — date_part http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT

Available units

• century ⚠
• decade ⚠
• day_of_month
• day_of_week
• day_of_year
• seconds_since_epoch
• hour
• iso_day_of_week
• iso_year
• microsecond
• millenium ⚠
• millisecond
• minute
• month
• quarter
• second
• timezone ⚠
• timezone_hour ⚠
• timezone_minute ⚠
• week
• year

• "dt_SQL_add" — Addition and interval http://www.postgresql.org/docs/current/static/functions-datetime.html#OPERATORS-DATETIME-TABLE

To be clear, it ends up looking like: ("some_column" + 5 * interval '1 minute')

Available units

• century ⚠
• decade ⚠
• day
• hour
• microsecond ⚠
• millisecond
• minute
• month
• second
• week
• year

🐬 "MySQL"

• "utc_now" — UTC_TIMESTAMP https://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_utc-timestamp
• "dt_SQL_pluck" — EXTRACT https://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_extract

Available units

• microsecond
• second
• minute
• hour
• day_of_month
• week
• month
• quarter
• year
• second_microsecond ⚠
• minute_microsecond ⚠
• minute_second ⚠
• hour_microsecond ⚠
• hour_second ⚠
• hour_minute ⚠
• day_microsecond ⚠
• day_second ⚠
• day_minute ⚠
• day_hour ⚠
• year_month ⚠

• "dt_SQL_add" — DATE_ADD https://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-add

Available units

• microsecond
• second
• minute
• hour
• day
• week
• month
• quarter
• year

🔴 "Oracle"

• "utc_now" — sys_extract_utc(SYSTIMESTAMP) http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions167.htm
• "dt_SQL_pluck" — EXTRACT

Available units

• second
• minute
• hour
• day_of_month
• month
• year

• "dt_SQL_add" — Addition and NUMTODSINTERVAL http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions103.htm

To be clear, it ends up looking like: ("some_column" + NUMTODSINTERVAL(4, 'MINUTE')

Available units

• second
• minute
• hour
• day

---

👑 CONTRIBUTORS 👑

These people worked on the original implementation, and thus deserve some credit for at least providing me a reference to implement this based off of. Give them their flowers, they earned them. 💐

• Alexander Hartmaier (abraxxa) for Oracle implementation details
• Devin Austin (dhoss) for Pg implementation details
• Rafael Kitover (caelum) for providing a test environment with lots of DBs

---

🤔 WHENCE dt_SQL_diff? 🤔

The original implementation of these date helpers (originally dubbed date operators) included a third operator called "diff". It existed to subtract one date from another and return a duration.

After using it a few times and getting bitten every time 😭, I decided to stop using it and instead compare against actual dates always. If someone can come up with a good use case I am interested in re-implementing "dt_SQL_diff", but I worry that it will be very unportable and generally not very useful. So like… it's on read, bestie. 📵

---

💖 METHODS 💖

Okay bestie, this is the part you actually came for. All the cute lil date-and-time helpers your ResultSet gets. Let's go one by one, no skipping, fr fr. ✨

🌸 utc 🌸

$rs->search({
  'some_date' => $rs->utc($datetime),
})->all

You hand it a DateTime object, it goes in and updates the time_zone to UTC, then formats it however your specific database engine likes its dates. It's giving "I'll translate for you, babe" energy. 🫶

Careful though! It dies if you pass it a date whose time_zone is floating. Not "warns", not "side-eyes you" — full-on dies. So don't do that. 💀

🌈 utc_now 🌈

Returns a ScalarRef that represents the way to get the current date and time in UTC for whatever database engine you're using. Basically "what time is it right now" but in your DB's love language. 🕐✨

🦋 dt_before 🦋

$rs->dt_before({ -ident => '.start' }, { -ident => '.end' })->all

Takes two values, and each one is an expression of TYPES. (You remember TYPES, right? We covered that. iykyk. 💅)

🎀 dt_on_or_before 🎀

$rs->dt_on_or_before({ -ident => '.start' }, DateTime->now)->all

Same deal: two values, each an expression of TYPES.

🧚 dt_on_or_after 🧚

$rs->dt_on_or_after(DateTime->now, { ident => '.end' })->all

Two values, each an expression of TYPES. Noticing a pattern? It's a vibe. ⭐

💜 dt_after 💜

$rs->dt_after({ ident => '.end' }, $rs->get_column('datecol')->as_query)->all

Two values, each an expression of TYPES. Yeah. Same energy as the last three. They're a lil family. 👑

🔥 dt_SQL_add 🔥

# which ones start in 3 minutes?
$rs->dt_on_or_after(
   { ident => '.start' },
   $rs->dt_SQL_add($rs->utc_now, 'minute', 3)
)->all

Takes three arguments: a date conforming to TYPES, a unit, and an amount. The whole idea is you add the given unit to the datetime. Which units are actually accepted? Bestie, that depends on your IMPLEMENTATION — go check there. 🫡

🍓 dt_SQL_subtract 🍓

Literally the same as dt_SQL_add, except it subtracts the amount instead of adding it. Math but make it cute. ➖✨

Real talk: this one is only confirmed to work with Postgres, MySQL and SQLite. It should work with Oracle and MSSQL, but because nobody had access to those DB engines, the implementation was done purely off the docs. So like... proceed with awareness, babe. 🙈

This method was implemented by Thomas Klausner https://domm.plix.at and sponsored by Ctrl O https://www.ctrlo.com/. Shoutout, iconic behavior. 🫶👑

⭐ dt_SQL_pluck ⭐

# get count per year
$rs->search(undef, {
   columns => {
      count => '*',
      year  => $rs->dt_SQL_pluck({ -ident => '.start' }, 'year'),
   },
   group_by => [$rs->dt_SQL_pluck({ -ident => '.start' }, 'year')],
})->hri->all

Takes two arguments: a date conforming to TYPES, and a unit. The idea is you pluck a given unit out of the datetime. Like pulling the "year" out of a full timestamp. And once again — which units are accepted? That's your IMPLEMENTATION's call. Go look. 💅

---

✨ AUTHOR ✨

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

👑 COPYRIGHT AND LICENSE 👑

This software is copyright (c) 2024 by Arthur Axel "fREW" Schmidt.

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.

---

and that's the whole page bestie!! 🎉 You just read DBIx::...thods1(3pm) as shipped with perl v5.40.0, dated 2024-11-16 — the User Contributed Perl Documentation version, no cap. Go forth and query your datetimes with main character energy. 🦋💖 mwah! 💅