SQL date ranges

Mason_Wheeler

Let's say I have a table like this:

create table MY_DATA(
   [DATE] datetime NOT NULL,
   [KEY] varchar(32) NOT NULL,
   [VALUE] int NOT NULL,
   primary key ([DATE], [KEY])
)

For any given day, any given key may or may not have an entry.

What I want is to retrieve all values for a given key within a certain date range, with nulls for the dates where that key has no value.

This would be trivial if I had a table containing all dates which I could left-join against, but I don't, and I don't want to create one. Is there any other way to do this in SQL, or do I have to munge the data client-side?

Vixen

You could probably do this server side with a temporary table and a cursor or loop to generate entries in the temporary table then left joining across...

that's probably the best way to do it if you want it server side. or you could just get the data that is there and have the client magick up the null entries.......

PleegWat

I think there are databases which have a first-class way to generate a list of values without a backing table, but for the big ones you have to resort to tricks. You haven't stated which engine you're using, but I know for oracle you can do

select level
from dual
connect by level <= 10

A quick google for ms sql server gives:

WITH nums AS
   (SELECT 1 AS value
    UNION ALL
    SELECT value + 1 AS value
    FROM nums
    WHERE nums.value < 10)
SELECT *
FROM nums

Since I seem to recall even SQL by itself is turing-complete, every database is going to have a trick like that. Performance isn't going to be ideal though.

Mason_Wheeler

@PleegWat said in SQL date ranges:

You haven't stated which engine you're using

MySQL

Mason_Wheeler

@Mason_Wheeler said in SQL date ranges:

@PleegWat said in SQL date ranges:

You haven't stated which engine you're using

MySQL

...and that scared everyone off, I see.

boomzilla

@Mason_Wheeler said in SQL date ranges:

@Mason_Wheeler said in SQL date ranges:

@PleegWat said in SQL date ranges:

You haven't stated which engine you're using

MySQL

...and that scared everyone off, I see.

I use Oracle and am familiar with the sorts of tricks that @PleegWat mentioned but I gave up on those a long time ago and took the route you already said you didn't want to use of creating a table of days.

It seems kind of silly, I know, but it really does make stuff like this pretty trivial to do. My recommendation is to reconsider that option.

svieira

I'm with (am?) @boomzilla - you can use a recursive CTE as @PleegWat suggests for MS SQL and that should work for MySQL as well, but a date table can't go wrong

MySQL: Generate a sequential range of numbers for time series analysis · Advanced SQL · SILOTA

loopback0

@Mason_Wheeler said in SQL date ranges:

@Mason_Wheeler said in SQL date ranges:

MySQL

...and that scared everyone off, I see.

We use proper databases.

Just create a table of dates as @boomzilla said.

iKnowItsLame

unbeknownst how MySQL handles <datetime> and MSSQL being my daily driver:
do you really want to create a "star-table" (that's what we call it here) with all possible datetimes down to the millisecond?
-> that will be a quite a lot of entries there

... that sounds wrong to me.

Mason_Wheeler

@iKnowItsLame No, not all datetimes. All dates. Which is a much more reasonably-sized data set to work with.

iKnowItsLame

@Mason_Wheeler
ahh... hmm, let me try this on my trusty MySQL 3.23 :-D