[email protected]
PL EN
Porady. Pandas. Python. Django. Microsoft Power BI. Tableau.

BigQuery. Jak prawidłowo obliczyć różnicę dni i uniknąć pułapki z EXTRACT?

| cloud | date_diff | bigquery

Podczas pracy z danymi w BigQuery często musimy obliczyć, ile dni minęło między dwiema datami – na przykład od daty zakupu do dziś. Funkcja DATE_DIFF świetnie się do tego nadaje, ale kryje się tu pewna pułapka, szczególnie gdy operujemy na kolumnach typu TIMESTAMP. Zobaczmy zatem, jak dobrze operować tą funkcją.

Problem: Dlaczego EXTRACT może nas zwieść?

⇒ Uwaga: dane wzięte z serwisu DataCamp.com i kursu “Introduction to BigQuery”.

Załóżmy, że w tabeli ecommerce.ecomm_order_details mamy kolumnę order_purchase_timestamp z dokładnym czasem zakupu. Chcemy sprawdzić, ile dni upłynęło od każdego zamówienia do bieżącej daty.

Pierwszym odruchem może być użycie EXTRACT(DAY FROM order_purchase_timestamp) i porównanie wyniku z CURRENT_DATE():

-- UWAGA: TO NIEPRAWIDŁOWE PODEJŚCIE!
SELECT
  order_id,
  DATE_DIFF(
      EXTRACT(DAY FROM order_purchase_timestamp), -- BŁĄD: Zwraca tylko dzień miesiąca (np. 15)
      CURRENT_DATE(),
      DAY
    ) AS roznica_dni
FROM
  ecommerce.ecomm_order_details
LIMIT 5;

Co jest nie tak?

Funkcja EXTRACT(DAY FROM order_purchase_timestamp) zwraca jedynie numer dnia miesiąca (np. 1, 15, 31) jako liczbę całkowitą. Tymczasem DATE_DIFF wymaga dwóch pełnych dat typu DATE. Porównanie liczby, takiej jak 15, z datą, np. 2025-05-25, spowoduje błąd, ponieważ BigQuery nie wie, jak traktować tę liczbę jako datę.

Rozwiązanie: Poprawna konwersja TIMESTAMP na DATE

Aby obliczyć różnicę prawidłowo, musimy najpierw skonwertować order_purchase_timestamp na typ DATE za pomocą funkcji DATE():

-- PRAWIDŁOWE ROZWIĄZANIE
SELECT
  order_id,
  order_purchase_timestamp,
  DATE_DIFF(
      CURRENT_DATE(),                 -- Data późniejsza
      DATE(order_purchase_timestamp), -- Data wcześniejsza, po konwersji z timestamp
      DAY                             -- Różnica liczona w dniach
    ) AS dni_od_zakupu
FROM
  ecommerce.ecomm_order_details
LIMIT 5;

Jak to działa?

  1. DATE(order_purchase_timestamp): Funkcja ta usuwa część czasową z timestampu (np. 2023-11-15 10:30:00 UTC), pozostawiając tylko datę (2023-11-15).
  2. CURRENT_DATE(): Zwraca aktualną datę.
  3. DATE_DIFF(CURRENT_DATE(), DATE(order_purchase_timestamp), DAY): Funkcja działa na dwóch pełnych datach, obliczając ich różnicę w dniach. Pamiętajmy, że DATE_DIFF(data1, data2, CZĘŚĆ_DATY) wykonuje operację data1 - data2. Jeśli data1 jest późniejsza, wynik będzie dodatni.

Podsumowanie

Pracując z datami w BigQuery, pamiętajmy o zgodności typów danych. Gdy mamy do czynienia z TIMESTAMP i chcemy użyć DATE_DIFF, pamiętajmy, by zawsze konwertować timestamp na DATE za pomocą funkcji DATE(). Dzięki temu unikniemy błędów, a nasze analizy będą dokładne i niezawodne!