SQL query (in MySQL)

Question

I have a table with potentially very many records (above 1 million) that contains simple data along with a timestamp that holds the moment of registration (defining CURRENT_TIMESTAMP as the default value for that field).

I want to implement a simple query that scans the table only once and counts the number of records that were register, say, the last 24 hours, the last 7 days and the last month.

Was trying this with no results (getting all zeros though I should get few 1's for sure):

SET @1 = CURRENT_TIMESTAMP() ;
SET @2 = @1 - INTERVAL 24 HOUR ;
SET @3 = @1 - INTERVAL  7 DAY  ;
SET @4 = @1 - INTERVAL  1 MONTH;

select Registration_Timestamp , (case Registration_Timestamp when  Registration_Timestamp> @2 THEN 1 else 0 end) as LAST_DAY  ,
                                (case Registration_Timestamp when  Registration_Timestamp> @3 THEN 1 else 0 end) as LAST_WEEK ,
                                (case Registration_Timestamp when  Registration_Timestamp> @4 THEN 1 else 0 end) as LAST_MONTH  
 from tbl_Dummy 
where Registration_Timestamp >= @4 ;

This is not going to give the sum of course, but once I get this to show 1s I will wrap it with select count(LAST_DAY),count(LAST_WEEK),count(LAST_MONTH) which will do the job of course.

Where is my error here?

Arth · Accepted Answer · 2016-05-17 16:25:53Z

1

Your CASE statements are slightly out:

 CASE *expr1* 
    WHEN *expr2* THEN *expr3*
    ELSE 0 
 END

Compares expr1 to expr2 and returns expr3 when expr1 = expr2

Your first CASE actually compares Registration_Timestamp (a timestamp) to the result of Registration_Timestamp> @2 (1 for true, 0 for false) which isn't what you want.

You want this form of CASE:

CASE  
    WHEN *expr1* THEN *expr2*
    ELSE 0 
END

Which returns expr2 when expr1 is true

In your first CASE:

CASE WHEN Registration_Timestamp > @2 THEN 1 ELSE 0 END

Alternatively

MySQL actually returns 1 and 0 already for conditionals. It's not hugely portable but you could use:

SELECT Registration_Timestamp > @2 AS LAST_DAY

Note

COUNT(*expr*) will count 1 for every NOT NULL result, which will count 1s and 0s the same, so you'd need SUM() here.

Or you could change your CASEs to:

CASE WHEN Registration_Timestamp > @2 THEN 1 END

Which will return NULL when the condition is not met.

edited May 17, 2016 at 16:25

answered May 17, 2016 at 16:22

Arth

13.1k5 gold badges43 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

FDavidov Over a year ago

Of course!!!! Stupid me!!! Thank you man!!!! The CASE WHEN Registration_Timestamp > @2 THEN 1 ELSE 0 END is best of course.

Arth Over a year ago

@FDavidov No problem, happy to help. Please upvote/accept the answer if you found it useful or correct! :)

FDavidov Over a year ago

Thanks again @Arth. Done.

muglio · Accepted Answer · 2016-05-17 16:25:03Z

0

You don't need the case statement. Just select the boolean expression directly and you will get the results you are looking for.

/*My Test Data*/

INSERT INTO regtable(RegDate) VALUES(CURRENT_TIMESTAMP() - INTERVAL 12 HOUR);
INSERT INTO regtable(RegDate) VALUES(CURRENT_TIMESTAMP() - INTERVAL 24 HOUR);
INSERT INTO regtable(RegDate) VALUES(CURRENT_TIMESTAMP() - INTERVAL 26 HOUR);
INSERT INTO regtable(RegDate) VALUES(CURRENT_TIMESTAMP() - INTERVAL 5 DAY);
INSERT INTO regtable(RegDate) VALUES(CURRENT_TIMESTAMP() - INTERVAL 7 DAY);
INSERT INTO regtable(RegDate) VALUES(CURRENT_TIMESTAMP() - INTERVAL 8 DAY);
INSERT INTO regtable(RegDate) VALUES(CURRENT_TIMESTAMP() - INTERVAL 29 DAY);
INSERT INTO regtable(RegDate) VALUES(CURRENT_TIMESTAMP() - INTERVAL 1 MONTH);
INSERT INTO regtable(RegDate) VALUES(CURRENT_TIMESTAMP() - INTERVAL 2 MONTH);


/* My SQL */
SELECT CURRENT_TIMESTAMP() into @ts;

select Id, RegDate, 
       RegDate > @ts - INTERVAL 24 HOUR as LAST_DAY,
       RegDate > @ts - INTERVAL 7 DAY as LAST_WEEK,
       RegDate > @ts - INTERVAL 1 MONTH as LAST_MONTH
  from regtable
 WHERE RegDate >= @ts - INTERVAL 1 MONTH

RESULTS

Id  RegDate                LAST_DAY LAST_WEEK LAST_MONTH
 1  2016-05-17 04:13:29     1        1         1
11  2016-05-16 16:13:30     0        1         1
21  2016-05-16 14:13:30     0        1         1
31  2016-05-12 16:13:30     0        1         1
41  2016-05-10 16:13:30     0        0         1
51  2016-05-09 16:13:30     0        0         1
61  2016-04-18 16:13:30     0        0         1

answered May 17, 2016 at 16:25

muglio

2,06718 silver badges19 bronze badges

2 Comments

FDavidov Over a year ago

Also very nice indeed, but I prefer Arth's suggestion for portability reasons (I thing that Oracle, for instance, would not return 1 and 0 (I'm not 100% sure though). Many thanks anyway.

muglio Over a year ago

The case statements result in branching in the CPU. You shouldn't add case statements when they are not needed.

Collectives™ on Stack Overflow

SQL query (in MySQL)

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related