[CALCITE-5180] Implement BigQuery functions for DATE, TIME, TIMESTAMP, DATETIME

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Labels: pull-request-available
• Assignee: Will Noble
• Reporter: Julian Hyde
• Created: 04/Jun/22 22:29
• Updated: 02/Mar/23 23:58

Description

Implement missing BigQuery functions for DATE, TIME, TIMESTAMP, DATETIME.

Functions include:

• CURRENT_DATE, CURRENT_DATETIME, CURRENT_TIME with timezone;
• EXTRACT with time unit values WEEK(weekday), DATE, TIME;
• various DATE, TIME, TIMESTAMP, DATETIME constructor functions;
• DATE_ADD, DATETIME_ADD, TIME_ADD, TIMESTAMP_ADD;
• DATE_SUB, DATETIME_SUB, TIME_SUB, TIMESTAMP_SUB;
• DATE_DIFF, DATETIME_DIFF, TIME_DIFF, TIMESTAMP_DIFF;
• DATE_TRUNC, DATETIME_TRUNC, TIME_TRUNC, TIMESTAMP_TRUNC;
• LAST_DAY;
• FORMAT_DATE, FORMAT_DATETIME, FORMAT_TIME, FORMAT_TIMESTAMP;
• PARSE_DATE, PARSE_DATETIME, PARSE_TIME, PARSE_TIMESTAMP.

The following functions are already implemented: CURRENT_DATETME, DATE(string), DATE_FROM_UNIX_DATE, TIMESTAMP_MICROS, TIMESTAMP_MILLIS, TIMESTAMP_SECONDS, UNIX_MICROS, UNIX_MILLIS, UNIX_SECONDS, UNIX_DATE.

Issue Links

• is a parent of: CALCITE-5498 BigQuery TIMESTAMP() function short notation for timezone offsets isn’t supported in Java 8
• is a parent of: CALCITE-5447 Add DATE_TRUNC for BigQuery
• is a parent of: CALCITE-5543 Implement BigQuery functions for parsing DATE, TIME, TIMESTAMP, DATETIME
• is related to: CALCITE-759 Add DayOfWeek and other missing date/time functions
• is related to: CALCITE-3218 Syntax error while parsing DATEADD function (which is valid on Redshift)
• relates to: CALCITE-5290 Add DATE_TRUNC function consistent with Postgres
• links to: GitHub Pull Request #2913
• links to: GitHub Pull Request #2973
• links to: GitHub Pull Request #3023
• links to: GitHub Pull Request #3039

Sub-Tasks

• Implement BigQuery TIME_TRUNC and TIMESTAMP_TRUNC functions: Closed
• Implement FORMAT_TIMESTAMP, FORMAT_DATE, FORMAT_TIME, FORMAT_DATETIME functions (enabled in BigQuery library): Closed
• Implement TIMESTAMP_ADD function (compatible with BigQuery): Closed
• Implement TIMESTAMP_DIFF function (compatible with BigQuery): Closed
• Implement BigQuery TIME_ADD/TIME_DIFF: Closed
• Implement DATE_SUB, TIME_SUB, TIMESTAMP_SUB (compatible w/ BigQuery): Closed
• Allow EXTRACT() to accept ISOWEEK and WEEK(WEEKDAY): In Progress
• Implement BigQuery TIME() Function: Closed
• Implement BigQuery DATE_ADD/DATE_DIFF: Closed
• Implement BigQuery DATETIME_ADD/DATETIME_DIFF: Closed
• Implement BigQuery DATETIME_SUB: Closed