Re: fastes way to retrieve segmented without using loop

Yes, I agree. I was a bit confused by the term "time" column. Not mention, the O/S and PG version were not given.

If column t is truly type time, then only 24 rows can be returned regardless of limit, as in this Universe, there are only 24 hrs in time.

However, if t is a timestamp, that is a whole other animal and the DISTINCT would have to be adjusted to include date & hour.

Perhaps if we were given a more accurate table structure, a more exact solution could be provided.

On Sun, Aug 23, 2015 at 6:09 PM, John McKown <john.archie.mckown@gmail.com> wrote:

Melvin's use of DISTINCT ON (...) is superior to my use of DISTINCT(...) because it doesn't return the value to your program. I keep forgetting this way. I learned it the other way. Old dog + new trick == problem.

On Sun, Aug 23, 2015 at 5:04 PM, John McKown <john.archie.mckown@gmail.com> wrote:

On Sun, Aug 23, 2015 at 4:05 PM, Tom Smith <tomsmith1989sk@gmail.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

--

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

--

Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.