بیگ کوئری از چهار گروه تابع مربوط به تاریخ و زمان DATE، TIME، DATETIME، و TIMESTAMP پشتیبانی می کند. این 4 گروه شامل توابع خاصتری مانند CURRENT_DATETIME، DATE_SUB، EXTRACT، FORMAT_TIME و غیره هستند. این توابع به کاربران اجازه می دهد تا انواع دادههای مربوط به تاریخ و زمان را در BigQuery به راحتی استخراج و ویرایش کنند. به عنوان مثال، با استفاده از این توابع میتوان بخشی از عبارت تاریخ یا زمان را استخراج کرد، یک فاصله زمانی به تاریخ یا زمان اضافه کرد و غیره. با توجه به اینکه تنوع و کارکرد هریک از این توابع متنوع و گاها گیج کننده است، در این مطلب به بررسی توابع مربوط به تاریخ در بیگ کوئری و کاربرد هریک میپردازیم.
انواع دادههای مربوط به تاریخ و زمان در BigQuery
قبل از آنکه به بررسی انواع توابع مربوط به تاریخ و زمان در بیگ کوئری بپردازیم، بهتر است که با انواع داده در بیگ کوئری در رابطه با تاریخ و زمان آشنا شوید:
توابع DATE، TIME، DATETIME، و TIMESTAMP در BigQuery SQL
در زیر جدولی از چهار گروه تابع تاریخ و زمان در BigQuery و همچنین توابع فرعی آنها آورده شده است.
دریافت تاریخ امروز در بیگ کوئری
برای دریافت عبارت تاریخ یا زمان امروز می توانید از تابع CURRENT در BigQuery استفاده کنید. برای استفاده از این تابع، از ساختار زیر استفاده کنید:
CURRENT_DATE()
CURRENT_DATETIME()
CURRENT_TIMESTAMP()
CURRENT_TIME()
مثال زیر نحوه استفاده از تابع CURRENT_DATETIME را برای دریافت تاریخ امروز و زمان حال نشان می دهد.
نحوه تغییر فرمت تاریخ در بیگ کوئری
هنگامی که با دادههای مربوط به تاریخ و زمان سروکار دارید، ممکن است بخواهید فرمتی که داده در آن ظاهر میشود را با استفاده از تابع FORMAT تغییر دهید. به عنوان مثال، بعد از اتصال آنالیتیکس 4 به بیگ کوئری، در بخش اسکیمای جدول آنالیتیکس متوجه میشوید که اطلاعات مربوط به event_date به صورت رشته در بیگ کوئری ذخیره می شوند. با توجه به اینکه خروجی تابع FORMAT از جنس رشته است؛ به راحتی میتوانید از این تابع همراه با ستون event_date استفاده کنید.
برای استفاده از این تابع، از ساختار زیر استفاده کنید:
FORMAT_DATE(format_string, date)
FORMAT_DATETIME(format_string, datetime)
FORMAT_TIMESTAMP(format_string, timestamp,[timezone])
FORMAT_TIME(format_string, time)
در مثال زیر با استفاده از تابع FORMAT، نحوه نمایش تاریخ را تغییر دادهایم:
فرمت رشته “%x” تاریخ را به صورت MM/DD/YY نشان می دهد.
اضافه یا کم کردن تاریخ و زمان در بیگ کوئری
در BigQuery میتوانیم عملیاتی مانند اضافه کردن یک سال به تاریخ، کم کردن یک هفته، اضافه کردن یک ساعت یا دقیقه به یک زمان و غیره را اجرا کنیم.
اضافه کردن به تاریخ یا زمان در بیگ کوئری
برای اضافه کردن به تاریخ و زمان در BigQuery باید از دستورات زیر استفاده کنید:
DATE_ADD(date_expression, INTERVAL int64_expression date_part)
DATETIME_ADD(datetime_expression, INTERVAL int64_expression date_part)
TIMESTAMP_ADD(timestamp_expression, INTERVAL int64_expression date_part)
TIME_ADD(time_expression, INTERVAL int64_expression date_part)
- با استفاده از interval برای بیگ کوئری مشخص میکنیم که قصد داریم دیتای مربوط به تاریخ یا زمان را به چه صورتی تغییر دهیم.
- int64_expression حاوی مقادیری است که از (9,223,372,036,854,775,808- تا 9,223,372,036,854,775,807-) متغیر است.
- به جای بخش date_part میتوانید از یکی از مقادیر DAY، HOUR، MINUTE، SECOND، MILLISECOND، MICROSECOND، WEEK، QUARTER، MONTH و YEAR استفاده کنید.
به عنوان مثال، در کوئری زیر از تابع DATE_ADD برای اضافه کردن یک روز به تاریخ استفاده میکنیم:
کم کردن از تاریخ یا زمان در بیگ کوئری
برای کم کردن از تاریخ و زمان در BigQuery باید از دستورات زیر استفاده کنید:
DATE_SUB(date_expression, INTERVAL int64_expression part)
DATETIME_SUB(datetime_expression, INTERVAL int64_expression part)
TIMESTAMP_SUB(timestamp_expression, INTERVAL int64_expression part)
TIME_SUB(time_expression, INTERVAL int64_expression part)
- با استفاده از interval برای بیگ کوئری مشخص میکنیم که قصد داریم دیتای مربوط به تاریخ یا زمان را به چه صورتی تغییر دهیم.
- int64_expression حاوی مقادیری است که از (9,223,372,036,854,775,808- تا 9,223,372,036,854,775,807-) متغیر است.
- به جای بخش date_part میتوانید از یکی از مقادیر DAY، HOUR، MINUTE، SECOND، MILLISECOND، MICROSECOND، WEEK، QUARTER، MONTH و YEAR استفاده کنید.
به عنوان مثال، در کوئری زیر از تابع DATETIME_SUB برای کم کردن یک روز از تاریخ استفاده میکنیم:
نحوه گروه بندی تاریخ/زمان در BigQuery
هنگام ارزیابی دادههای تاریخ/زمان در BigQuery، میتوانیم دادههای خود را بر اساس بخشهای مختلف تاریخ مانند دقیقه، ساعت، روز، سال، هفته و غیره سازماندهی و گروهبندی کنیم. برای انجام این کار میتوانید از دستورات زیر استفاده کنید:
DATE_TRUNC(date_expression, date_part)
DATETIME_TRUNC(datetime_expression, date_part)
TIMESTAMP_TRUNC(timestamp_expression, date_part)
TIME_TRUNC(time_expression, date_ part)
- به جای بخش date_part میتوانید از یکی از مقادیر DAY، HOUR، MINUTE، SECOND، MILLISECOND، MICROSECOND، WEEK، QUARTER، MONTH، YEAR، DAYOFWEEK، DAYOFYEAR، ISOWEEK و ISOYEAR استفاده کنید.
در مثال زیر از تابع DATE_TRUNC برای برگرداندن اولین روز ماه استفاده میکنیم.
SELECT
DATE_TRUNC(DATE ‘2021-07-25’, MONTH) AS first_day_of_month;
علاوه بر این، به جای استفاده از تابع TRUNC، می توانید از تابع LAST DAY برای بدست آوردن آخرین روز هر date_part استفاده کنید.
LAST_DAY(date_expression, [date_part])
LAST_DAY(datetime_expression, [date_part])
مثال زیر از تابع LAST_DAY برای برگرداندن آخرین روز هفته که از یکشنبه شروع می شود استفاده می کند.
SELECT
LAST_DAY(DATETIME ‘2021-07-10 11:45:00’, WEEK(SUNDAY))
7-last-day
نحوه استخراج بخشی از تاریخ و زمان در بیگ کوئری
میتوانید بخشی از تاریخ را از داده تاریخ خود استخراج کنید، مانند بررسی زمان ورود کاربر یا بررسی آمار کلی ماهانه برای بررسی اینکه کدام ماهها فروش بیشتری دارند. برای اجرای چنین عملیاتی میتوانید از دستورات زیر استفاده کنید:
EXTRACT(part FROM date_expression)
EXTRACT(part FROM datetime_expression)
EXTRACT(part FROM timestamp_expression)
EXTRACT(part FROM time_expression)
- به جای بخش date_part میتوانید از یکی از مقادیر DAY، HOUR، MINUTE، SECOND، MILLISECOND، MICROSECOND، WEEK، QUARTER، MONTH، YEAR، DAYOFWEEK، DAYOFYEAR، ISOWEEK و ISOYEAR استفاده کنید.
مثال زیر از تابع EXTRACT برای برگرداندن مقدار دقیقه استفاده می کند.
SELECT
EXTRACT(MINUTE FROM DATETIME(“2020-12-25 15:30:00”)) as minute;
8-extract
چگونه تفاوت بین دو تاریخ یا زمان را بیگ کوئری محاسبه کنیم؟
برای محاسبه تفاوت بین دو تاریخ، میتوانید از دستورات زیر استفاده کنید:
DATE_DIFF(date_expression_a, date_expression_b, part)
DATETIME_DIFF(datetime_expression_a, datetime_expression_b, part)
TIMESTAMP_DIFF(timestamp_expression_a, timestamp_expression_b, part)
TIME_DIFF(time_expression_a,time_expression_b, part)
در مثال زیر از تابع DATE_DIFF برای محاسبه تفاوت ماه بین دو تاریخ استفاده شده است:
SELECT
DATE “2021-12-15 12:30:10” as first_date,
DATE “2021-07-15 17:45:33” as second_date,
DATE_DIFF(DATE “2021-12-15”,
DATE “2021-07-15”, MONTH) as month_difference;
9-date-diff
نحوه تبدیل انواع دادههای تاریخ و زمان در BigQuery
با تابع CAST میتوانید نوع یک داده را به یک نوع دیگر تبدیل کنیم. نحوه استفاده از تابع CAST به صورت زیر است:
CAST(expression AS datatype)
تبدیل رشته به تاریخ یا زمان
SELECT
CAST(’19:30:12′ AS TIME) AS time
10-cast-string
تبدیل datetime به date
SELECT
CAST(CURRENT_DATETIME() AS DATE);
11-cast-datetime-date
توجه: برای انجام این کار میتوانید از DATE(datetime expression) هم استفاده کنید.
عملگرهای موجود برای مقایسه تاریخ در بیگ کوئری
در BigQuery، گاهی اوقات ممکن است نیاز به مقایسه تاریخ و زمان برای انجام عملیاتی مانند دریافت دادههای هفته قبل یا در محدوده خاصی با استفاده از عملگرهای مختلف مقایسه داشته باشید:
<, <=, >, >=, = , != or <>, [NOT] BETWEEN, [NOT] LIKE, [NOT] IN
مثال زیر از عملگرهای >= و < برای دریافت تمام تاریخ های مشخص شده بین دو تاریخ استفاده می شود:
SELECT
date
FROM
(
SELECT
CAST(‘2021-07-02’ AS DATE) AS date
UNION ALL
( SELECT
CAST(‘2021-07-11’ AS DATE) AS date)
UNION ALL
( SELECT
CAST(‘2021-10-02’ AS DATE) AS date)
) AS table_3
WHERE
((date >= ‘2021-07-02’) AND (date < ‘2021-07-30’))
12-date-comparison
