CODEWITHSUNDEEP
mysqlnode.jsdatetime
yanman1234
yanman1234

Reputation: 1019

Select data of all datetimes

I currently have two queries, one which displays all data (regardless of time), another which considers a time frame. I'm trying to condense these together and was wondering if mysql has something for dateTimes which acts as an "allTime" variable, that is when plugged into:
select count(*) from table where (color = "red") and (dateMade between "allTime" and "allTime");
would display the row count of where color is red, for all rows in the table. A solution I thought of was to just use a minimum date of 0000-01-01 00:00:00 and some large end bound date, but if there already exists something to solve this I thought I might as well use that.

EDIT: This would be used on more than just the example query above, with most being much longer and more complex. The purpose is to combine this with node to make functions simpler and not have to consider a special case of all data, rather just use the time frame query and return all data based on the bounds given as arguments

Upvotes: 0

Views: 46

Answers (3)

weirdan
weirdan

Reputation: 2632

For cases when you don't need the date range you could make part of the where clause a true-ish noop:

select ...
from tablename
where date_created between $start and $end or $start = $end

Then, if you need the unbounded query, just pass the same $start and $end to it.

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 94913

Fill the variables @from and/or @to with NULL, if you don't want to restrict the data. Use IFNULL to check if the restriction is to be applied or not.

select count(*) 
from table 
where color = @color
and datemade between ifnull(@from, datemade) and ifnull(@to, datemade);

Upvotes: 1

Paul Maxwell
Paul Maxwell

Reputation: 35583

Don't pass the data twice unless you really have to, you could use a "conditional aggregation" instead, e.g.

select
    count(*) all_count
  , count(case when dateMade between @start and @end then id end) period_count
from table 
where (color = "red")

To get "all time" you simply do not filter by date range in the where clause.

Note too that COUNT() function increments for any non-null value it encounters, so for the case expression it should return a column that will always be present in a row such as the primary key.

An alternative is to use SUM() instead, like this

select
    count(*) all_count
  , sum(case when dateMade between @start and @end then 1 else 0 end) period_count
from table 
where (color = "red")

Upvotes: 0

Related Questions