Open dinodeSimon opened 10 months ago
Moved to pg-aggregates plugin repo.
In the mean time, please apply the recommendations from this guide to the documentation: https://postgraphile.org/postgraphile/next/migrating-from-v4/migrating-custom-plugins
The code below was created as a poc with benjie to work with V5 and could possibly be used to update the docs
// Constants from PostgreSQL
const TIMESTAMP_OID = "1114";
const TIMESTAMPTZ_OID = "1184";
const DATE_OID = "1082";
// Determine if a given type is a timestamp/timestamptz
/** @type {(pgType: import('postgraphile/@dataplan/pg').PgCodec) => boolean} */
const isTimestamp = (pgType) =>
pgType.extensions.oid === TIMESTAMP_OID ||
pgType.extensions.oid === TIMESTAMPTZ_OID;
// Determine if a given type is a date
/** @type {(pgType: import('postgraphile/@dataplan/pg').PgCodec) => boolean} */
const isDatestamp = (pgType) => {
pgType.extensions.oid === DATE_OID;
};
// Build a spec that truncates to the given interval
/** @type{(sql: any, interval: any) => import('@graphile/pg-aggregates').AggregateGroupBySpec} s*/
const tsTruncateSpec = (sql, interval) => ({
// `id` has to be unique, derive it from the `interval`:
id: `truncated-to-${interval}`,
// Only apply to timestamp fields:
isSuitableType: isTimestamp,
// Given the column value represented by the SQL fragment `sqlFrag`, wrap it
// with a `date_trunc()` call, passing the relevant interval.
sqlWrap: (sqlFrag) =>
sql.fragment`date_trunc(${sql.literal(interval)}, ${sqlFrag})`,
});
const dsTruncateSpec = (sql, interval) => ({
// `id` has to be unique, derive it from the `interval`:
id: `date-truncated-to-${interval}`,
// Only apply to timestamp fields:
isSuitableType: isDatestamp,
// Given the column value represented by the SQL fragment `sqlFrag`, wrap it
// with a `date_trunc()` call, passing the relevant interval.
sqlWrap: (sqlFrag) =>
sql.fragment`date_trunc(${sql.literal(interval)}, ${sqlFrag})`,
});
// This is the PostGraphile plugin; see:
// https://www.graphile.org/postgraphile/extending/
/** @type {GraphileConfig.Plugin} */
const DateTruncAggregateGroupSpecsPlugin = {
name: "DateTruncAggregateGroupSpecsPlugin",
after: ["PgAggregatesSpecsPlugin"],
schema: {
hooks: {
build(build) {
const { pgSql: sql } = build;
build.pgAggregateGroupBySpecs = [
// Copy all existing specs, except the ones we're replacing
...build.pgAggregateGroupBySpecs.filter(
(spec) =>
!["truncated-to-day", "truncated-to-hour"].includes(spec.id)
),
// Add our timestamp specs
tsTruncateSpec(sql, "quarter"),
tsTruncateSpec(sql, "year"),
tsTruncateSpec(sql, "month"),
tsTruncateSpec(sql, "week"),
tsTruncateSpec(sql, "day"),
tsTruncateSpec(sql, "hour"),
tsTruncateSpec(sql, "minute"),
// Add our datestamp specs
dsTruncateSpec(sql, "quarter"),
dsTruncateSpec(sql, "year"),
dsTruncateSpec(sql, "month"),
dsTruncateSpec(sql, "week"),
dsTruncateSpec(sql, "day"),
// Other values: microseconds, milliseconds, second, minute, quarter,
// decade, century, millennium.
// See https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC
];
return build;
},
},
},
};
const DateTruncAggregateGroupSpecsPreset = {
plugins: [DateTruncAggregateGroupSpecsPlugin],
};
export default DateTruncAggregateGroupSpecsPreset;
(Edited code to use triple backticks ```ts
for code formatting)
@jemgillam please can you integrate this into the v5
branch of the pg-aggregates plugin README? https://github.com/graphile/pg-aggregates/tree/v5
Feature description
The example provided on https://github.com/graphile/pg-aggregates#defining-your-own-grouping-derivatives to add month / day / quarter etc to timestamp fields use the V4 APIs and need to be updated to support V5