search

goccy / bigquery-emulator

BigQuery emulator server implemented in Go
MIT License
846 stars 108 forks source link

DATE_TRUNC with ISO_WEEK #244

Open ambujs-ps opened 1 year ago

ambujs-ps commented 1 year ago

Hello,

Getting this error: failed to scan rows: currently unsupported DATE_TRUNC with ISO_WEEK

Any chance of adding this feature?

Thanks in advance!

ohaibbq commented 7 months ago

@totem3 @goccy This was fixed in the last release of go-zetasqlite https://github.com/goccy/go-zetasqlite/commit/b6dd6a5c9272ce2c804b47446646d42ebdf9450a

totem3 commented 7 months ago

@ohaibbq Thank you! I confirmed that it works in the latest bigquery-emulator.

But when I checked this, I noticed that the results do not match what was expected.

In the ISO week, the week starts on Monday. So, for a date 2024/04/02, which is a Tuesday, we would expect the start of the week to be 2024/04/01, which is the nearest Monday. Indeed, BigQuery returns this expected result. However, currently, a different result is being returned as follows.

emulator

cat <<QUERY | bq query --nouse_legacy_sql --project_id test --api http://localhost:9050 
WITH x AS (
    SELECT
        dt
    FROM
        UNNEST(
            GENERATE_DATE_ARRAY(
                '2024-04-01',
                '2024-04-30',
                INTERVAL 1 DAY)
            ) as dt
        )
SELECT dt, DATE_TRUNC(dt, ISOWEEK) FROM x
QUERY
+------------+------------+
|     dt     |   $col2    |
+------------+------------+
| 2024-04-01 | 2024-03-07 |
| 2024-04-02 | 2024-03-07 |
| 2024-04-03 | 2024-03-07 |
| 2024-04-04 | 2024-03-07 |
| 2024-04-05 | 2024-03-07 |
| 2024-04-06 | 2024-03-07 |
| 2024-04-07 | 2024-03-07 |
| 2024-04-08 | 2024-03-14 |
| 2024-04-09 | 2024-03-14 |
| 2024-04-10 | 2024-03-14 |
| 2024-04-11 | 2024-03-14 |
| 2024-04-12 | 2024-03-14 |
| 2024-04-13 | 2024-03-14 |
| 2024-04-14 | 2024-03-14 |
| 2024-04-15 | 2024-03-21 |
| 2024-04-16 | 2024-03-21 |
| 2024-04-17 | 2024-03-21 |
| 2024-04-18 | 2024-03-21 |
| 2024-04-19 | 2024-03-21 |
| 2024-04-20 | 2024-03-21 |
| 2024-04-21 | 2024-03-21 |
| 2024-04-22 | 2024-03-28 |
| 2024-04-23 | 2024-03-28 |
| 2024-04-24 | 2024-03-28 |
| 2024-04-25 | 2024-03-28 |
| 2024-04-26 | 2024-03-28 |
| 2024-04-27 | 2024-03-28 |
| 2024-04-28 | 2024-03-28 |
| 2024-04-29 | 2024-04-04 |
| 2024-04-30 | 2024-04-04 |
+------------+------------+

BigQuery

cat <<QUERY | bq query --nouse_legacy_sql --project_id test
WITH x AS (
    SELECT
        dt
    FROM
        UNNEST(
            GENERATE_DATE_ARRAY(
                '2024-04-01',
                '2024-04-30',
                INTERVAL 1 DAY)
            ) as dt
        )
SELECT dt, DATE_TRUNC(dt, ISOWEEK) FROM x
QUERY
+------------+------------+
|     dt     |    f0_     |
+------------+------------+
| 2024-04-01 | 2024-04-01 |
| 2024-04-02 | 2024-04-01 |
| 2024-04-03 | 2024-04-01 |
| 2024-04-04 | 2024-04-01 |
| 2024-04-05 | 2024-04-01 |
| 2024-04-06 | 2024-04-01 |
| 2024-04-07 | 2024-04-01 |
| 2024-04-08 | 2024-04-08 |
| 2024-04-09 | 2024-04-08 |
| 2024-04-10 | 2024-04-08 |
| 2024-04-11 | 2024-04-08 |
| 2024-04-12 | 2024-04-08 |
| 2024-04-13 | 2024-04-08 |
| 2024-04-14 | 2024-04-08 |
| 2024-04-15 | 2024-04-15 |
| 2024-04-16 | 2024-04-15 |
| 2024-04-17 | 2024-04-15 |
| 2024-04-18 | 2024-04-15 |
| 2024-04-19 | 2024-04-15 |
| 2024-04-20 | 2024-04-15 |
| 2024-04-21 | 2024-04-15 |
| 2024-04-22 | 2024-04-22 |
| 2024-04-23 | 2024-04-22 |
| 2024-04-24 | 2024-04-22 |
| 2024-04-25 | 2024-04-22 |
| 2024-04-26 | 2024-04-22 |
| 2024-04-27 | 2024-04-22 |
| 2024-04-28 | 2024-04-22 |
| 2024-04-29 | 2024-04-29 |
| 2024-04-30 | 2024-04-29 |
+------------+------------+