Re: fastes way to retrieve segmented without using loop

On Sun, Aug 23, 2015 at 4:05 PM, Tom Smith <tomsmith1989sk(at)gmail(dot)com> wrote:

> Hello:
>
> I have a time series table,
> using below sql, loop (psque code), I can get one row for each hour
>

​s/psque/pseudo/g;​

>
> for( H=1: H< 99999; H++){
> select * from table where t >= H and t < H+1 limit 1
> }
>
> t (time column) is indexed).
>
> Is there a better way to use a issue a SINGLE SQL
> with an array of time start/end pair
> so it will be executed once to send back result, which would be much faster
> than issuing sql again and again (even with prepared statement and using
> sql function).
>
> Thanks in Advance
>

Well, I have a bit of a problem if "t" is a "time column". Do you mean a
"time interval"? Or do you really mean it is an integer of some sort. I ask
because H sure looks like a plain old integer to me.

In any case, if "t" is an "int" as opposed to a "time interval", then you
could start with something like:

SELECT DISTINCT(t), ???? columns ???? FROM table WHERE t BETWEEN 1 AND
99999;

But if "t" really is a "time interval" in the PostgreSQL sense, and H is
like the "hour" portion (H --> Hour, makes sense to this weirdo). And you
want to select one row of data where the "t" interval is 1 hour, another
where the "t" interval is 2 hours, another where the "t" interval is 3
hours, and so on up to an interval of at most 99_999 hours. Then you might
need something like:

SELECT DISTINCT(EXTRACT(HOUR FROM t)) AS interval_truncated_to_hour, * FROM
table WHERE t BETWEEN 1 AND 99999;

I don't know of a way to eliminate the first field from the result. But, in
reality, I would _never_ issue a SELECT * in a "normal" program. Only ask
for the columns you are actually going to need. Because, someday, someone,
is going to change the schema on the table and your program is (im)pure
porcine excrement at that point. With no errors returned to it. IMO, it is
an unshakable rule to ONLY and ALWAYS specify the variable names. The only
exception is if your program actually examines the schema of the table
before doing a SELECT and dynamically constructs it.

--

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown