CODEWITHSUNDEEP
mysqlsql
user1432124
user1432124

Reputation:

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

Now i want to select date

  1. in days if it is 3 days before or less
  2. in hours if it is 24 hours before or less
  3. in minutes if it is 60 minutes before or less
  4. in seconds if it is 60 seconds before or less
  5. in simple format if it is before 3days or more

OUTPUT

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

UPDATE My sql query

  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

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

Upvotes: 1

Views: 173

Answers (1)

juergen d
juergen d

Reputation: 204746

Use the timestampdiff function for that and 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

Upvotes: 1

Related Questions