Goal: add the following functionalities to PyDough, which should be tested e2e (see test_pipeline.py for examples):

• DATEDIFF(x, y, "years") returns y-x in years (December 31st of 2009 and January 1st of 2010 count as 1 year apart).
• DATEDIFF(x, y, "months") returns y-x in months (January 31st of 2014 and February 1st of 2014 count as 1 month apart).
• DATEDIFF(x, y, "days") returns y-x in days (11:59 pm of one day vs 12:01 am of the next day count as 1 day apart).
• DATEDIFF(x, y, "hours") returns y-x in hours (6:59 pm vs 7:01 pm of the same day count as 1 hour apart).
• DATEDIFF(x, y, "minutes") returns y-x in minutes (same idea as hours).
• DATEDIFF(x, y, "seconds") returns y-x in seconds (same idea as hours).

The default, without unit, should be "days". Any other unit should raise an exception.

How these should be converted for SQLite:

• For second/minute/hour/day, truncate x and y to the corresponding unit using date, take the difference in their julianday values after truncation & multiply accordingly. Everything after the truncation can be done with the SQLGlot DateDiff function. For example:
  • original code is DATEDIFF(x, y, "days") -> sqlglot code is DATEDIFF(this=DATE(y, 'start of day'), expression=DATE(x, 'start of day'), unit=VAR('DAY')) -> sqlite text is CAST(JULIANDAY(DATE(y, 'start of day')) - JULIANDAY(DATE(x, 'start of day')) AS INTEGER)
  • original code is DATEDIFF(x, y, "hours") -> sqlglot code is DATEDIFF(this=DATE(y, 'start of hour'), expression=DATE(x, 'start of hour'), unit=VAR('HOUR')) -> sqlite text is CAST(JULIANDAY(DATE(y, 'start of hour')) - JULIANDAY(DATE(x, 'start of hour')) * 24.0 AS INTEGER)
• For year, truncate x to the start of the year using date, take the difference in years. For example:
  • original code is DATEDIFF(x, y, "years") -> becomes the equivalent of CAST(strftime('%Y', date(y, 'start of year')) AS INTEGER) - CAST(strftime('%Y', date(x, 'start of year')) AS INTEGER)
• For month, do the same process as year, multiple hte answer by 12, then do the same process but with month, and add the two
  • original code is DATEDIFF(x, y, "months") -> becomes the equivalent of 12 * (CAST(strftime('%Y', date(y, 'start of year')) AS INTEGER) - CAST(strftime('%Y', date(x, 'start of year')) AS INTEGER)) + CAST(strftime('%m', date(y, 'start of month')) AS INTEGER) - CAST(strftime('%m', date(x, 'start of month')) AS INTEGER)

All of these features need to be documented in the function list documentation