mysql 帮助尝试比较时间

Question

我正准备向我的 mysql 数据库添加一个新列('close_date')，该列是根据日期时间字段('scrape')计算的。

具体来说，如果字段抓取的时间(即日期时间)> 下午 6 点，则将 close_date 设置为抓取日期，时间为 23:59:00。否则将 close_date 设置为抓取间隔 -1 天，时间为 23:59:00。

我无法比较工作时间。

这是 fiddle 模式...

drop table if exists mytable;
create table mytable (
scrape datetime,
average_rating int,
number_of_ratings int,
factored_rating int);
insert into mytable
select '2014-05-29 00:13:55',5,2,3 union all
select '2014-05-29 00:14:42',4,1,5 union all
select '2014-05-29 21:08:18',12,3,1 union all
select '2014-05-30 00:14:46',11,4,2 union all
select '2014-05-30 21:08:22',8,2,12;

这是 SQL...

SELECT
    scrape,
    DATE_FORMAT(scrape,'%H:%i:%s') TIMEONLY,
    DATE_FORMAT(STR_TO_DATE('18:00:00','%T'),'%T') SIXPM,
    @x := IF(@TIMEONLY > @SIXPM, DATE_FORMAT(scrape, '%Y-%m-%d 23:59:00'), DATE_ADD(scrape, INTERVAL -1 DAY)) close_date
FROM mytable

结果:

SCRAPE                  TIMEONLY    SIXPM           CLOSE_DATE
May, 29 2014 00:13:55+0000  00:13:55    18:00:00    2014-05-28 00:13:55
May, 29 2014 00:14:42+0000  00:14:42    18:00:00    2014-05-28 00:14:42
May, 29 2014 21:08:18+0000  21:08:18    18:00:00    2014-05-28 21:08:18
May, 30 2014 00:14:46+0000  00:14:46    18:00:00    2014-05-29 00:14:46
May, 30 2014 21:08:22+0000  21:08:22    18:00:00    2014-05-29 21:08:22

即使 TIMEONLY 大于 SIXPM，IF 也始终为 FALSE。

感谢您的见解!

Answer 1

我明白了。 TIMEONLY 字段不可用于 IF 比较，因此我必须创建另一个临时字段 (@tim)...

SELECT
    scrape,
    @tim := DATE_FORMAT(scrape,'%H:%i:%s'),
    @six := DATE_FORMAT(STR_TO_DATE('18:00:00','%T'),'%T'),
    IF(@tim > @six, DATE_FORMAT(scrape, '%Y-%m-%d 23:59:00'), DATE_FORMAT(DATE_ADD(scrape, INTERVAL -1 DAY),'%Y-%m-%d 23:59:00')) close_date
FROM mytable

现在的结果正是我所期望的:

SCRAPE                      CLOSE_DATE
May, 29 2014 00:13:55+0000  May, 28 2014 23:59:00
May, 29 2014 00:14:42+0000  May, 28 2014 23:59:00
May, 29 2014 21:08:18+0000  May, 29 2014 23:59:00
May, 30 2014 00:14:46+0000  May, 29 2014 23:59:00
May, 30 2014 21:08:22+0000  May, 30 2014 23:59:00

附:我最终通过 python 进行了字段更新:

for row in cur:
    print row
    stkid = row[0]
    # check if after 5pm close...
    if int(row[1].strftime("%H")) > 17:
        cldt = row[1].strftime("%Y-%m-%d")
    else:
        # else it's in the wee hours, so we need to subtract a day
        cldt = (row[1] - relativedelta(days=1)).strftime("%Y-%m-%d")
    print "greater than five pm...", row[1].strftime("%H"), row, cldt