Geek Answers Handbook

Date classified as "yesterday", "last week," etc.

I have a table like this:

// mytable +----+------------+ | id | date_time | +----+------------+ | 1 | 1464136759 | -- 5 days ago | 2 | 1464436759 | -- 2 days ago | 3 | 1464538248 | -- 6 hours ago +----+------------+ -- ^ these are based on current time which is 1464561158

I also have this query:

 SELECT id, CASE DATE(FROM_UNIXTIME(date_time)) WHEN CURDATE() THEN 'today' WHEN CURDATE() - INTERVAL 1 DAY THEN 'yesterday' WHEN CURDATE() - INTERVAL 7 DAY THEN 'in last week' ELSE 'in last month or more' END range FROM mytable WHERE 1

And here is the current output:

 +----+---------------+ | id | range | +----+---------------+ | 1 | in last month | | 2 | in last month | | 3 | yesterday | +----+---------------+

As you can see, my question selects all these unix-times errors. Why and how can I fix this?

Here's the expected result:

 +----+--------------+ | id | range | +----+--------------+ | 1 | in last week | | 2 | yesterday | | 3 | today | +----+--------------+
+5
source share
2 answers

Presumably, the logic you want looks like this:

 SELECT id, (CASE WHEN FROM_UNIXTIME(date_time) >= CURDATE() THEN 'today' WHEN FROM_UNIXTIME(date_time) >= DATE_SUB(CURDATE(), INTERVAL 1 DAY) THEN 'yesteray' WHEN FROM_UNIXTIME(date_time) >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) THEN 'in last week' ELSE 'in last month or more' END) as `range` FROM mytable WHERE 1;

Notes:

  • There is no reason to retrieve the date.
  • The classes in the case are executed sequentially, so the first matching one returns a value.
  • If your source code does not work, it may cause a crash. Your source code should not have been returned just yesterday.
  • range is a reserved word, so it needs to be escaped.

Here is the SQL script.

+3
source

The problem is that you do not check the range for a week in your third state. Value...

 DATE(FROM_UNIXTIME(date_time)) BETWEEN CURDATE() - INTERVAL 7 DAY AND CURDATE() - INTERVAL 1 DAY

Fiddle

Your code currently says WHEN CURDATE() - INTERVAL 7 DAY THEN 'in last week' if the date is 7 days ago ONLY . The 25th is not the 22nd, so it does not pass the test. You need to specify a range in order to make it work.

+2
source

More articles:


All Articles