如何根据条件从数据库中获取时间 [英] How to get time from db depending upon conditions

问题描述

我有一张表，其中的值是

I have a table in which the value are

             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

现在我要选择日期

1. 如果是 3 天前或更短，则以天为单位
2. 如果早于 24 小时或更短，则以小时为单位
3. 如果早于 60 分钟或更短，则以分钟为单位
4. 以秒为单位，如果它早于 60 秒或更短时间
5. 如果是在 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

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

Only problem is i am unable to use break and default in sql like switch case in c++

推荐答案

使用 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 

这篇关于如何根据条件从数据库中获取时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！