Sequelize use output of one function into another (date

I have to convert a postgres query to Sequelize query.

Below query is working to get weekly summary. But the start day is coming as Monday.

SELECT date_trunc('week', date::date) AS "weekly", COUNT(DISTINCT(date)) AS "working_days" FROM "public"."employees" AS "Employee" WHERE ("Employee"."deleted_at" IS NULL) GROUP BY "weekly" LIMIT 100;

Code:

const dateTruncFunc = fn("date_trunc", "week", literal("date::date"));

const result = await Employee.findAll({
    limit: 100,
    attributes: [
        [dateTruncFunc, "weekly"],
        [literal("COUNT(DISTINCT(date))"), "working_days"],
    ],
    group: ["weekly"],
    raw: true,
});
console.log(result);

Output:

[
  { weekly: 2021-11-15T00:00:00.000Z, working_days: '1' },
  { weekly: 2021-11-22T00:00:00.000Z, working_days: '1' },
  { weekly: 2021-12-13T00:00:00.000Z, working_days: '2' },
  { weekly: 2021-12-20T00:00:00.000Z, working_days: '4' },
  { weekly: 2021-12-27T00:00:00.000Z, working_days: '2' },
  { weekly: 2022-01-03T00:00:00.000Z, working_days: '3' },
  { weekly: 2022-01-10T00:00:00.000Z, working_days: '6' },
  { weekly: 2022-01-17T00:00:00.000Z, working_days: '7' },
  { weekly: 2022-01-24T00:00:00.000Z, working_days: '7' },
  { weekly: 2022-01-31T00:00:00.000Z, working_days: '1' }
]

I want to have query to start the day from thursday

SELECT date_trunc('week', date::date) - Interval '4 days' AS "weekly", COUNT(DISTINCT(date)) AS "working_days" FROM "public"."employees" AS "Employee" WHERE ("Employee"."deleted_at" IS NULL) GROUP BY "weekly" LIMIT 100;

Code:

const dateTruncFunc = fn("date_trunc", "week", literal("date::date"));
const intervalLiteral = literal(" - Interval '4 days'");

const result = await Employee.findAll({
    attributes: [
        [fn("concat", dateTruncFunc, intervalLiteral), "weekly"],
        [literal("COUNT(DISTINCT(date))"), "working_days"],
    ],
    group: ["weekly"],
    raw: true,
});
console.log(result);

Generated Query:

SELECT concat(date_trunc('week', date::date), - Interval '4 days') AS "weekly", COUNT(DISTINCT(date)) AS "working_days" FROM "public"."employees" AS "Employee" WHERE ("Employee"."deleted_at" IS NULL) GROUP BY "weekly" LIMIT 100;

Output:

[
  { weekly: '2021-11-15 00:00:00 00-4 days', working_days: '1' },
  { weekly: '2021-11-22 00:00:00 00-4 days', working_days: '1' },
  { weekly: '2021-12-13 00:00:00 00-4 days', working_days: '2' },
  { weekly: '2021-12-20 00:00:00 00-4 days', working_days: '4' },
  { weekly: '2021-12-27 00:00:00 00-4 days', working_days: '2' },
  { weekly: '2022-01-03 00:00:00 00-4 days', working_days: '3' },
  { weekly: '2022-01-10 00:00:00 00-4 days', working_days: '6' },
  { weekly: '2022-01-17 00:00:00 00-4 days', working_days: '7' },
  { weekly: '2022-01-24 00:00:00 00-4 days', working_days: '7' },
  { weekly: '2022-01-31 00:00:00 00-4 days', working_days: '1' }
]

The output is coming wrong because I am doing concatenation of date_trunc output to literal which is wrong.

Searched all resources to get the same problem but was unable to find it.

I need to merge the string representation of both the functions together and execute it.

Please Help!

CodePudding user response：

You need to place both date_trunc and Interval pieces into a single Literal:

const truncWithIntervalLiteral = literal("date_trunc('week', date::date),  - Interval '4 days'");

const result = await Employee.findAll({
    attributes: [
        [truncWithIntervalLiteral , "weekly"],
        [literal("COUNT(DISTINCT(date))"), "working_days"],
    ],
    group: ["weekly"],
    raw: true,
});