Hello to all and specially SQL GURUs,
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
where Registration_Timestamp >= @4 ;
CASE statements are slightly out:
CASE *expr1* WHEN *expr2* THEN *expr3* ELSE 0 END
expr2 and returns
expr1 = expr2
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 WHEN *expr1* THEN *expr2* ELSE 0 END
expr1 is true
In your first
CASE WHEN Registration_Timestamp > @2 THEN 1 ELSE 0 END
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
COUNT(*expr*) will count 1 for every
NOT NULL result, which will count 1s and 0s the same, so you'd need
Or you could change your
CASE WHEN Registration_Timestamp > @2 THEN 1 END
Which will return
NULL when the condition is not met.