Take value of some date that fall in specific hours in weekday but take different date in specific hours in weekend

user_11879339 0
Viewed 51

I want to take the value that falls between the current day (today) and yesterday but only when after 9 am yesterday and before 9 am today. The current day must be only on Tuesday - Friday. But, if the current day is Monday, it will take value from Friday after 9 am to Monday before 9 am.

Samples

+---------+------------------------------+
|   ID    |   registration_started_at    |
+---------+------------------------------+
|    1    |  2021-05-13 07:00:00.000     |    
|    2    |  2021-05-13 11:00:00.000     |
|    3    |  2021-05-14 08:00:00.000     |
|    4    |  2021-05-14 10:00:00.000     |    
|    5    |  2021-05-15 12:00:00.000     |
|    6    |  2021-05-16 13:00:00.000     |
|    7    |  2021-05-17 08:00:00.000     | 
|    8    |  2021-05-17 10:00:00.000     |    
+---------+------------------------------+

So let say when the current_day (today) is Friday (14 May 2021), When I run the query it must return

Desired Result 1

+---------+------------------------------+
|   ID    |   registration_started_at    |
+---------+------------------------------+   
|    2    |  2021-05-13 11:00:00.000     |
|    3    |  2021-05-14 08:00:00.000     |     
+---------+------------------------------+

But when current_day (today) is Monday (17 May 2021) it should return

Desired Result 2

+---------+------------------------------+
|   ID    |   registration_started_at    |
+---------+------------------------------+
|    4    |  2021-05-14 10:00:00.000     |    
|    5    |  2021-05-15 12:00:00.000     |
|    6    |  2021-05-16 13:00:00.000     |
|    7    |  2021-05-17 08:00:00.000     |    
+---------+------------------------------+

I only manage to get the desired result 1 with this query and I think this still not correct tho for desired result 2

SELECT ID,
       DATETIME(registration_started_at, 'Asia/Jakarta') as registration_started_at
FROM `table`
WHERE 
       DATETIME_DIFF(CURRENT_DATETIME('Asia/Jakarta'), DATETIME(registration_started_at, 'Asia/Jakarta'), week) = 0
       AND DATE(DATETIME_ADD(DATETIME(registration_started_at, 'Asia/Jakarta'), INTERVAL -9 HOUR)) = CURRENT_DATE('Asia/Jakarta') - 1

---------edit 1

Using the Mr. Caius Jard answer

WHERE
(DATETIME(registration_started_at, 'Asia/Jakarta') BETWEEN (
        (CASE
            WHEN EXTRACT(
                DAYOFWEEK
                FROM CURRENT_DATE('Asia/Jakarta')
            ) = 2 -- if Monday
            THEN DATETIME_ADD(
                CURRENT_DATETIME('Asia/Jakarta'),
                INTERVAL -63 HOUR
            ) -- then 63 hours back from midnight today
            ELSE DATETIME_ADD(
                CURRENT_DATETIME('Asia/Jakarta'),
                INTERVAL -15 HOUR
            )
        END)
    ) -- else 15 hours back from midnight today
    AND DATETIME_ADD(
        CURRENT_DATETIME('Asia/Jakarta'),
        INTERVAL 9 HOUR
    )) -- 9am today

It returns the 63 hours before today's time for Monday or 15 hours before today's time if not Monday, which is incorrect because if I run the query on 15.00 it only returns value from 00.00 today

google-bigquery sql
3 Answers

Perhaps something like:

WHERE 
   registration_started_at 
     BETWEEN 
       CASE WHEN EXTRACT(DAYOFWEEK FROM CURRENT_DATE()) = 2 -- if Monday
       THEN DATETIME_ADD(CURRENT_DATE(), INTERVAL -63 HOUR)) -- then 63 hours back from midnight today
       ELSE DATETIME_ADD(CURRENT_DATE(), INTERVAL -15 HOUR)) END -- else 15 hours back from midnight today
     AND
       DATETIME_ADD(CURRENT_DATE(), INTERVAL 9 HOUR)) -- 9am today

Never used bigquery, so it might need some fiddling, but the basic idea is that we ask via case when what the current day is and use it to change how much we go back in time

user_14106640

I think this captures the logic you want:

WHERE DATETIME(registration_started_at, 'Asia/Jakarta') < DATETIME_ADD(DATETIME(DATE(CURRENT_DATETIME('Asia/Jakarta'))), INTERVAL 9 HOUR) AND
      (EXTRACT(DAYOFWEEK, DATE(CURRENT_DATETIME('Asia/Jakarta'))) = 2 AND
       DATETIME(registration_started_at, 'Asia/Jakarta') > DATETIME_ADD(DATETIME_ADD(DATETIME(DATE(CURRENT_DATETIME('Asia/Jakarta'))), INTERVAL 9 HOUR), INTERVAL -3 DAY) OR
       DATETIME(registration_started_at, 'Asia/Jakarta') > DATETIME_ADD(DATETIME_ADD(DATETIME(DATE(CURRENT_DATETIME('Asia/Jakarta'))), INTERVAL 9 HOUR), INTERVAL -1 DAY)
      )

What are the important components of this?

This expressoin:

DATETIME_ADD(DATETIME(DATE(CURRENT_DATETIME('Asia/Jakarta'))), INTERVAL 9 HOUR)

Returns 9:00 on the current date in Jakarata. No matter what, you want registration_started_at before that date/time.

This expression

EXTRACT(DAYOFWEEK, DATE(CURRENT_DATETIME('Asia/Jakarta')))

Returns the current day of the week, with 2 for Monday.

These expressions:

DATETIME_ADD(DATETIME_ADD(DATETIME(DATE(CURRENT_DATETIME('Asia/Jakarta'))), INTERVAL 9 HOUR), INTERVAL -3 DAY)
DATETIME_ADD(DATETIME_ADD(DATETIME(DATE(CURRENT_DATETIME('Asia/Jakarta'))), INTERVAL 9 HOUR), INTERVAL -1 DAY)

Just subtract 1 or 3 days from the current 9:00 datetime.

user_11440350

Using Mr. Caius Jard idea, I am able to make it works. I just need to cast CURRENT_DATE to DATETIME() to take the today's 00:00

WHERE
(DATETIME(registration_started_at, 'Asia/Jakarta') BETWEEN (
        (CASE
            WHEN EXTRACT(
                DAYOFWEEK
                FROM CURRENT_DATE('Asia/Jakarta')
            ) = 2 -- if Monday
            THEN DATETIME_ADD(
                DATETIME(CURRENT_DATE('Asia/Jakarta')),
                INTERVAL -63 HOUR
            ) -- then 63 hours back from midnight today
            ELSE DATETIME_ADD(
                DATETIME(CURRENT_DATE('Asia/Jakarta')),
                INTERVAL -15 HOUR
            )
        END)
    ) -- else 15 hours back from midnight today
    AND DATETIME_ADD(
        DATETIME(CURRENT_DATE('Asia/Jakarta')),
        INTERVAL 9 HOUR
    )) -- 9am today
user_118793390
Related