根据当前日期选择日期

I have an Oracle SQL that selects a date range according to the current timestamp. The main idea is to select the records that happened (moveCompletedOn) in the date range between today at 0700 and 1900 hours, or between 1900 and 0700 of the next day, depending on the current timestamp at the moment the query runs.

我有一个Oracle SQL,它根据当前时间戳选择日期范围。主要思想是在今天的0700和1900之间,或者第二天的1900到0700之间的日期范围内选择发生的记录(moveCompletedOn),具体取决于查询运行时的当前时间戳。

UPDATE: For example:

更新:例如:

If the current timestamp is 10AM, then the selection must be: 0700 to 1900

如果当前时间戳是10AM,则选择必须是:0700到1900

If the current timestamp is 3PM, then the selection must be also: 0700 to 1900

如果当前时间戳是3PM,则选择也必须是:0700到1900

If the current timestamp is 10PM, the selection must be 1900 from today to 0700 next day

如果当前时间戳是晚上10点,那么从今天到第二天0700,选择必须是1900

If the cureent timestamp is 2AM, the selection must be 1900 from yesterday, to 0700

如果时间戳是凌晨2点,那么从昨天到0700,选择必须是1900

UPDATE 2: Clarification

更新2:澄清

Just to clarify a bit, the selection should be DAY shift and NIGHT shift. From 7AM to 7PM and from 7PM and 7AM of the next day.

只是为了澄清一点,选择应该是DAY shift和NIGHT shift。从早上7点到晚上7点以及第二天的晚上7点和早上7点。

Whichever the time the query is run, the current timestamp will fall between those two selections. I just want to select all the records that happened in a shift, depending on the time the query runs. If it runs during the DAY shift, it will select all records of the DAY shift. If it runs on the NIGHT shift, it will select all records of the NIGHT shift.

无论何时运行查询,当前时间戳都将落在这两个选择之间。我只想根据查询运行的时间选择班次中发生的所有记录。如果它在DAY班次期间运行,它将选择DAY班次的所有记录。如果它在NIGHT班次上运行,它将选择NIGHT班次的所有记录。

I hope is a bit clearer now. If not, let me know, please.

我希望现在有点清楚。如果没有,请告诉我。

Not being very good in SQL Server, I wonder how I can rewrite this piece of WHERE statement in SQL Server language:

在SQL Server中不是很好,我想知道如何在SQL Server语言中重写这段WHERE语句:

   WHERE moveCompletedOn BETWEEN 
   TO_DATE(TO_CHAR(SYSDATE - (12 / 24), 'DD-MON-YYYY ') ||  
      DECODE((TO_CHAR(SYSDATE - (12 / 24), 'HH24MI') / 1200), 0, '07:00', '19:00'), 'DD-MON-YYYY HH24:MI') AND 
   TO_DATE(TO_CHAR(SYSDATE - (12 / 24), 'DD-MON-YYYY ') ||  
      DECODE((TO_CHAR(SYSDATE - (12 / 24), 'HH24MI') / 1200), 0, '07:00', '19:00'), 'DD-MON-YYYY HH24:MI') + (11 / 24))

Any help will be appreciated.

任何帮助将不胜感激。

1 个解决方案

#1

In SQL server,I would do something like this

在SQL服务器中,我会做这样的事情

select * from aa
where last_update between 
DATEADD(HOUR, 
    case datepart(hour, getdate() ) 
    when 1 then -3 
    when 2 then -7
    else  -10
    end , 
    GETDATE() 
 ) 
and 
DATEADD(HOUR,  
    case datepart(hour, getdate() ) 
    when 1 then -1 
    when 2 then -5
    else  -5
    end , 
    GETDATE() 
) ;

You can change the values to fit your needs.

您可以更改值以满足您的需求。

#1