如何根据条件从数据库获取时间

admin

如何根据条件从数据库获取时间

sql

我有一个表，其中的值是

             Table_hello
date                            col2
2012-01-31 23:01:01             a
2012-06-2  12:01:01             b
2012-06-3  20:01:01             c

现在我想选择日期

如果少于或等于3天，则以天为单位
如果少于或等于24小时，则以小时为单位
如果少于或等于60分钟，则以分钟为单位
如果少于或等于60秒，则以秒为单位
如果是3天或更长时间，则以简单格式显示

输出

for row1 2012-01-31 23:01:01 
for row2 1 day ago 
for row3 1 hour ago

更新我的SQL查询

  select case 
            when TIMESTAMPDIFF(SECOND, `date`,current_timestamp) <= 60 
            then concat(TIMESTAMPDIFF(SECOND, `date`,current_timestamp), ' seconds')
            when TIMESTAMPDIFF(DAY, `date`,current_timestamp) <= 3 
            then concat(TIMESTAMPDIFF(DAY, `date`,current_timestamp), ' days')end
            when TIMESTAMPDIFF(HOUR, `date`,current_timestamp) <= 60 
            then concat(TIMESTAMPDIFF(HOUR, `date`,current_timestamp), ' hours')
            when TIMESTAMPDIFF(MINUTE, `date`,current_timestamp) <= 60 
            then concat(TIMESTAMPDIFF(MINUTE, `date`,current_timestamp), ' minutes')

from table_hello

唯一的问题是我无法像SQL中的switch case那样在sql中使用break和default

阅读 173

2021-07-01

共1个答案

admin

为此，请使用timestampdiff函数CASE：

select case 
        when TIMESTAMPDIFF(SECOND, `date`,current_timestamp) <= 60 
        then concat(TIMESTAMPDIFF(SECOND, `date`,current_timestamp), ' seconds')
        when TIMESTAMPDIFF(DAY, `date`,current_timestamp) <= 3 
        and TIMESTAMPDIFF(DAY, `date`,current_timestamp) >= 1 
        ...
       end as time_diff 
from Table_hello
where TIMESTAMPDIFF(DAY, `time`,current_timestamp) >= 3

2021-07-01