I have to choose randomly rows from a Post GRE Table within a given date-time range. They way I doing now is query the table within the date-time range and then randomly select the rows.(Please see below) This is becoming very inefficient in terms of querying as I have 10 GB of data within the range. Is there a better way to do this? Please advise

sp = pd.read_sql("SELECT * FROM table1 WHERE timestamp >= '"+sampling_start_date+"' and timestamp <= '"+sampling_end_date+"'", con)

random_subset = sp.sample(n=300)

Time Stamp format is as below

sampling_start_date = "2018-08-17 20:00:00"

Selecting A Random Number Of Rows From A Table

Random sample of rows can be selected using a random number SQL function. In PostgreSQL, for example, it is random().

The number of rows selected depends on the number of rows that would be selected without the random sampling, and the sampling probability,

For example, if the table contains 5,000 rows and the sampling probability is less than 0.1, about 500 rows will be selected (10% of 5,000).

If the WHERE clause, without the random sampling, would select, say, 1,500 rows, and the sampling probability is less than 0.2, then about 300 rows will be selected (20% of 1,500).

Notice that using this method you cannot guarantee the exact number of selected rows (that's the nature of probability...), so in order to get a number of rows close to what you desire you will have to appropriately choose the probability.

Also notice that if you want to repeat this process and get the same results every time, you'll have to seed the random number generator with the same value. You can do that with the setseed() function:

SELECT setseed(.123);

Last thing, the random() function exists in PostgeSQL. Other database engines might use a different name for that function (in MySQL and SQL Server, for example, I believe it is rand()).

See the following select statements for some examples.

-- all rows
select count(*) from my_table;
-- 5264

-- should get about half of all rows
select count(*) from my_table where random() < 0.5;
-- 2734

-- should get about 10% of all rows
select count(*) from my_table where random() < 0.1;
-- 513

-- all rows matching some criteria
select count(*) from my_table where id > 100000 and id < 400000;
-- 3023

-- about half of the rows matching the above criteria
select count(*) from my_table where id > 100000 and id < 400000 and random() < 0.5;
-- 1527

-- about 10% of the rows matching the above criteria
select count(*) from my_table where id > 100000 and id < 400000 and random() < 0.1;
-- 283