How to extract data when date is 7am or 7pm?-CodePudding

I have a SQL database table column called 'Date' with this format: 2022-06-06 00:00:00.0000000

I would like to know how to extract the data when Date is at 7am and 7pm. I figure using 'like' function but I can't figure out the condition to extract data.

Sample data:

CHAMBER_LIST    DATE    TEMPERATURE HUMIDITY    Z1_VOL  Z1B_VOL Z1C_VOL Z1_CUR  Z1B_CUR Z1C_CUR Z2_VOL  Z2B_VOL Z2C_VOL Z2_CUR  Z2B_CUR Z2C_CUR Z3_VOL  Z3B_VOL Z3C_VOL Z3_CUR  Z3B_CUR Z3C_CUR Z4_VOL  Z4B_VOL Z4C_VOL Z4_CUR  Z4B_CUR Z4C_CUR Z5_VOL  Z5B_VOL Z5C_VOL Z5_CUR  Z5B_CUR Z5C_CUR Z6_VOL  Z6B_VOL Z6C_VOL Z6_CUR  Z6B_CUR Z6C_CUR
SG_QRA_SGHAST_0043  2022-06-06 00:00:00.0000000 110.01  84.94   3.6 1.26    13.2    0   0   0   3.6 1.26    13.2    0   0   0   3.6 1.26    13.2    0   0   0   3.6 1.26    13.2    0   0   0   3.6 1.26    13.2    0   0   0   3.6 1.26    13.2    0   0   0
SG_QRA_SGHAST_0044  2022-06-06 00:00:00.0000000 109.98  85.02   3.6 1.95    1.17    0.06    1.2 0.17    0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0                       
SG_QRA_SGHAST_0046  2022-06-06 00:00:00.0000000 110.02  84.94   3.6 1.95    1.17    0   0.2 0.25    0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0

My desired result is to extract a row of data twice per day at 7am and 7pm from the live database table.

What I tried: FROM table_name WHERE Date LIKE '%s%s%s%s%s%s%s%s%s%s 07:00:00'

CodePudding user response：

I solved this using task schedular and running the data based on

datetime = current_timestamp

CodePudding user response：

Let's first say that using SQL key words as table name or column name should be avoided, so please rename the column "date" if possible.

An important question is: Do you really want to exactly check two single seconds per day? Even if this is data from a weather station that is present for every hour, are you sure the data will always get exactly this time and can't be delayed by one or two seconds?

Anyway, let's list some options here how you can check the time. In my example, the column is named "datecolumn" and the table is named "test". You will of course change this to your naming.

In case only the hour should be checked and minutes or seconds don't matter, you can do this using DATEPART:

SELECT datecolumn FROM test 
WHERE DATEPART(HOUR,datecolumn) IN (7,19)

The outcome will be everything from 07:00:00 to 07:59:59 and from 19:00:00 to 19:59:59.

Let's also check that the minute is 0:

SELECT datecolumn FROM test 
WHERE DATEPART(HOUR,datecolumn) IN (7,19)
AND DATEPART(MINUTE,datecolumn) = 0

The outcome will be everything from 07:00:00 to 07:00:59 and from 19:00:00 to 19:00:59.

If you really want to only get data for 07:00:00 and for 19:00:00, you can still use DATEPART:

SELECT datecolumn FROM test 
WHERE DATEPART(HOUR,datecolumn) IN (7,19)
AND DATEPART(MINUTE,datecolumn) = 0
AND DATEPART(SECOND,datecolumn) = 0

Since this is a bit long and bad to read, you should consider to go another way. Maybe you will prefer to do this:

SELECT datecolumn FROM test
WHERE CAST(datecolumn AS TIME) IN ('07:00:00','19:00:00')

I think this will be the shortest way for your original question. You could also do something like this:

SELECT datecolumn FROM test
WHERE DATEDIFF(SECOND,CAST(datecolumn AS date),datecolumn) IN (25200,68400)

I created a fiddle here which shows the differences, please try out: db<>fiddle

As you can see, there are many options to check this. You should really take care which option is the best in your case. Doing a very hard restriction on the exact time might miss a lot of data if this is few seconds "delayed". You will need to decide if this is intended.