SQL: select one record for each day closest to a specific time

Question

I have one table that stores values ​​from a point in time:

CREATE TABLE values
(
    value DECIMAL,
    datetime DATETIME 
)

There can be many values ​​in each day, there can also be only one value for a given day. Now I want to get a value for each day at a given time interval (for example, one month), which is closest to a given time of day. I want to get only one value per day if there are records for that day or if there is no value if there are no records. My database is PostgreSQL. I am completely stuck in this. I could just get all the values ​​in a time span, select the closest value for each day of the program, but that would mean pulling a huge amount of data from the database, because there can be many values ​​for one day.

(Update)

In order to formulate this a little more abstractly: I have data of arbitrary accuracy (maybe one minute, maybe two hours or two days), and I want to convert it to a fixed accuracy of up to one day, with a certain time a day.

(second update)

This is a request from the accepted answer with the correct converstions of type PostgreSQL, assuming the desired time will be 16:00:

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

Answer 1

How about going in that direction?

SELECT values.value, values.datetime
FROM values,
( SELECT DATE(datetime) AS date, MIN(ABS(_WANTED_TIME_ - TIME(datetime))) AS timediff
  FROM values
  GROUP BY DATE(datetime)
) AS besttimes
WHERE TIME(values.datetime) BETWEEN _WANTED_TIME_ - besttimes.timediff
                                AND _WANTED_TIME_ + besttimes.timediff
AND DATE(values.datetime) = besttimes.date

I'm not sure if the time / time and functions are abs (time), so you have to replace them.

Answer 2

It seems you have two parts:

• Are there any daily results?

• If so, which one is the closest?

1, , .

, , , , , PLSQL ( - ) .

, intervals . Postgres datetime , , , , , , .