日期和当前日期之间的区别，if语句？

I have a table with four columns:

我有一个有四列的表:

id;
datefrom;
dateto;
user id.

I need to calculate the difference between datefrom and dateto (datefrom-dateto) in hours, add this difference into additional column. The main thing is that some cells of dateto are empty. In this case I should take "dateto" as current date and time.

我需要以小时为单位计算datefrom和dateto(datefrom-dateto)之间的差异,将此差异添加到其他列中。主要的是日期的一些单元格是空的。在这种情况下,我应该将“dateto”作为当前日期和时间。

The second thing is to add column with if statment. If the difference between datefrom and dateto more than 3 hours than 1, else 0.

第二件事是使用if statment添加列。如果datefrom和date之间的差异超过3小时,则为0,否则为0。

Now I can calculate only differences in hours. The main problem is with empty cells and additional column with statement when hours more than 3

现在我只能计算小时差异。当小时数超过3时,主要问题是空单元格和附加列语句

select id, DateFrom, DateTo, UserId,
(Datefrom-DateTO) as hours
 from table

order by id

enter image description here

在此处输入图像描述

3 个解决方案

#1

Here is a good example for MS SQL:

这是MS SQL的一个很好的例子:

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate ='2007-06-05'
SET @EndDate ='2007-08-05'

SELECT DATEDIFF(Hour, @StartDate, @EndDate) AS NewDate
--Return Value = 1464 Hour


SELECT DATEDIFF(minute, @StartDate, @EndDate) AS NewDate
--Return Value = 87840 minute


SELECT DATEDIFF(second, @StartDate, @EndDate) AS NewDate
--Return Value = 5270400 second

#2

You will need some Control Flow Functions.
With the select, you will need:

您将需要一些控制流功能。选择后,您将需要:

SELECT id, datefrom, dateto, userid

You can use IFNULL(), if dateto is NULL, then it will return CURDATE():

你可以使用IFNULL(),如果dateto是NULL,那么它将返回CURDATE():

IFNULL(dateto, CURDATE())

And one IF(condition, truth, false), but first, you will need the diference in hours. You can calculate with some Date and Time Functions, you will get the diference between the dates, convert it to seconds, and divide by 3600):

一个IF(条件,真相,假),但首先,你需要几个小时的差异。您可以使用一些日期和时间函数计算,您将获得日期之间的差异,将其转换为秒,然后除以3600):

TIME_TO_SEC(TIMEDIFF(IFNULL(dateto, CURDATE()), datefrom)) / 3600

Now, check if it's bigger than 3:

现在,检查它是否大于3:

IF(TIME_TO_SEC(TIMEDIFF(IFNULL(dateto, CURDATE()), datefrom)) / 3600 > 3, 1, 0)

At the end, it will be like:

最后,它会像:

SELECT
  id,
  datefrom,
  dateto,
  userid,
  TIME_TO_SEC(TIMEDIFF(IFNULL(dateto, CURDATE()), datefrom)) / 3600
  IF(TIME_TO_SEC(TIMEDIFF(IFNULL(dateto, CURDATE()), datefrom)) / 3600 > 3, 1, 0),
FROM table;

#3

thanks all.

I wrote code in Periscope. I dont know why but I can use there some functions as TIME_TO_SEC, TIMEDIFF, TIMESTAMPDIFF.

我在Periscope中编写了代码。我不知道为什么,但我可以使用一些函数作为TIME_TO_SEC,TIMEDIFF,TIMESTAMPDIFF。

The final code is below.

最终代码如下。

SELECT id
, DateFrom
, DateTo
, UserId
, CASE 
 WHEN DateFrom is null OR (DateFrom - DateTo) < '0 days 00:00:00'
 THEN (NOW() - DateTo)
 ELSE (DateFrom - DateTo)   
 END as hours 
, CASE 
 WHEN (DateFrom IS NULL AND (NOW() - DateTo) > '0 days 03:00:00') OR (DateFrom is not null and ((DateFrom - DateTo) > '0 days 03:00:00') OR (DateFrom - DateTo) < '0 days 00:00:00')
 THEN 1
 ELSE 0 
 END as Attribute

#1