Translate MySQL date arithmetic with expressions to PostgreSQL

[ds-cbo]

I am translating a bunch of MySQL queries we have to PostgreSQL and so far this tool seems to do everything perfectly, but there's one warning which results a mismatch which I'd like to work on fixing.

For example, in MySQL we have:

select date_sub(date, interval(weekday(date)) day) from ...

In human language that comes down to "get the first day of the week of 'date'" (assuming weeks start on monday).

Converting this manually to PostgreSQL, I can only come up with

select date - ((extract(DOW from date)::integer + 6) % 7) from ...

Because where MySQL's weekday starts with monday=0, PostgreSQL's extract starts DOW with sunday=0.

Test cases

select date_sub(date, interval(weekday(date)) day) from (select '2025-11-02' as date union select '2025-10-31' union select '2025-10-27') _;

select date - ((extract(DOW from date)::integer + 6) % 7) from (select '2025-11-02'::date as date union select '2025-10-31'::date union select '2025-10-27'::date) _;

How would one go on to generalise this approach? Currently it gets stuck on

sqlglot/sqlglot/dialects/postgres.py

Lines 69 to 75 in a9d0f63

	e = self._simplify_unless_literal(expression.expression)
	if isinstance(e, exp.Literal):
	e.set("is_string", True)
	elif e.is_number:
	e = exp.Literal.string(e.to_py())
	else:
	self.unsupported("Cannot add non literal")

making the current output:

SELECT "date" - INTERVAL 'weekday DAY'

with token tree

Select(
  expressions=[
    DateSub(
      this=Column(
        this=Identifier(this=date, quoted=False)),
      expression=Paren(
        this=Anonymous(
          this=weekday,
          expressions=[
            Column(
              this=Identifier(this=date, quoted=False))])),
      unit=Var(this=DAY))])

I would be okay with adding support just for the weekday case, but I'm new to this codebase and don't quite know where to start. I imagine it'd first need to understand the WEEKDAY function of MySQL to parse it into a proper non-Anonymous structure. Or would it be okay to add something like

if isinstance(e, Anonymous) and e.this == "weekday" and isinstance(unit, Var) and unit.this == "DAY"

to patch specific support into it?

I read through the documentation but couldn't find a good starting point, so I'm looking for tips here.

[georgesittas]

Hey @ds-cbo, you would need to create a new Func subclass Weekday to properly parse MySQL's WEEKDAY function, ensuring that the arg_types is as generic as possible, so that it can represent all possible variants of WEEKDAY across dialects.

Then, you make sure it roundtrips unaffected in MySQL and finally add a generator for it in Postgres to produce the equivalent SQL. I think your example should just work after that, perhaps with a slight tweak of the Interval generator / parser, if needed.

Closing this one but feel free to reach out in our Slack for easier communication