Databases
StackOverflow2013
Postsdbo
POSQL: select one record for each day nearest to a specific time
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL: select one record for each day nearest to a specific time
    text
    copied!<p>I have one table that stores values with a point in time:</p> <pre><code>CREATE TABLE values ( value DECIMAL, datetime DATETIME ) </code></pre> <p>There may be many values on each day, there may also be only one value for a given day. Now I want to get the value for each day in a given timespan (e.g. one month) which is nearest to a given time of day. I only want to get one value per day if there are records for this day or no value if there are no records. My database is PostgreSQL. I'm quite stuck with that. I could just get all values in the timespan and select the nearest value for each day programmatically, but that would mean to pull a huge amount of data from the database, because there can be many values on one day.</p> <p>(Update)</p> <p>To formulate it a bit more abstract: I have data of arbitrary precision (could be one minute, could be two hours or two days) and I want to convert it to a fixed precision of one day, with a specific time of day.</p> <p>(second update)</p> <p>This is the query from the accepted answer with correct postgresql type converstions, assuming the desired time is 16:00:</p> <pre><code>SELECT datetime, value FROM values, ( SELECT DATE(datetime) AS date, MIN(ABS(EXTRACT(EPOCH FROM TIME '16:00' - CAST(datetime AS TIME)))) AS timediff FROM values GROUP BY DATE(datetime) ) AS besttimes WHERE CAST(values.datetime AS TIME) BETWEEN TIME '16:00' - CAST(besttimes.timediff::text || ' seconds' AS INTERVAL) AND TIME '16:00' + CAST(besttimes.timediff::text || ' seconds' AS INTERVAL) AND DATE(values.datetime) = besttimes.date </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload