Database design for time series

I'd go with tje single table approach, perhaps partitioned by year so that it becomes easy to get rid of old data.

Create an index like

CREATE INDEX ON a (date_trunc('hour', t + INTERVAL '30 minutes'));

Then use your query like you wrote it, but add

AND date_trunc('hour', t + INTERVAL '30 minutes')
  = date_trunc('hour', asked_time + INTERVAL '30 minutes')

The additional condition acts as a filter and can use the index.

You can use a UNION of two queries to find all timestamps closest to a given one:

(
  select t
  from a
  where t >= timestamp '2019-03-01 17:00:00'
  order by t
  limit 1
)
union all
(
  select t
  from a
  where t <= timestamp '2019-03-01 17:00:00'
  order by t desc
  limit 1
)

That will efficiently make use of an index on t. On a table with 10 million rows (~3 years of data), I get the following execution plan:

Append  (cost=0.57..1.16 rows=2 width=8) (actual time=0.381..0.407 rows=2 loops=1)
  Buffers: shared hit=6 read=4
  I/O Timings: read=0.050
  ->  Limit  (cost=0.57..0.58 rows=1 width=8) (actual time=0.380..0.381 rows=1 loops=1)
        Output: a.t
        Buffers: shared hit=1 read=4
        I/O Timings: read=0.050
        ->  Index Only Scan using a_t_idx on stuff.a  (cost=0.57..253023.35 rows=30699415 width=8) (actual time=0.380..0.380 rows=1 loops=1)
              Output: a.t
              Index Cond: (a.t >= '2019-03-01 17:00:00'::timestamp without time zone)
              Heap Fetches: 0
              Buffers: shared hit=1 read=4
              I/O Timings: read=0.050
  ->  Limit  (cost=0.57..0.58 rows=1 width=8) (actual time=0.024..0.025 rows=1 loops=1)
        Output: a_1.t
        Buffers: shared hit=5
        ->  Index Only Scan Backward using a_t_idx on stuff.a a_1  (cost=0.57..649469.88 rows=78800603 width=8) (actual time=0.024..0.024 rows=1 loops=1)
              Output: a_1.t
              Index Cond: (a_1.t <= '2019-03-01 17:00:00'::timestamp without time zone)
              Heap Fetches: 0
              Buffers: shared hit=5
Planning Time: 1.823 ms
Execution Time: 0.425 ms

As you can see it only requires very few I/O operations and that is pretty much independent of the table size.

The above can be used for an IN condition:

select *
from a
where t in ( 
  (select t
   from a
   where t >= timestamp '2019-03-01 17:00:00'
   order by t
   limit 1)
  union all
  (select t
   from a
   where t <= timestamp '2019-03-01 17:00:00'
   order by t desc
   limit 1)
);

If you know you will never have more than 100 values close to that requested timestamp, you could remove the IN query completely and simply use a limit 100 in both parts of the union. That makes the query a bit more efficient as there is no second step for evaluating the IN condition, but might return more rows than you want.

If you always look for timestamps in the same year, then partitioning by year will indeed help with this.

You can put that into a function if it is too complicated as a query:

create or replace function get_closest(p_tocheck timestamp)
  returns timestamp
as
$$
  select *
  from (
     (select t
     from a
     where t >= p_tocheck
     order by t
     limit 1)
    union all
    (select t
     from a
     where t <= p_tocheck
     order by t desc
     limit 1)
  ) x
  order by greatest(t - p_tocheck, p_tocheck - t)
  limit 1;
$$
language sql stable;

The the query gets as simple as:

select *
from a
where t = get_closest(timestamp '2019-03-01 17:00:00');

Another solution is to use the btree_gist extension which provides a "distance" operator <->

Then you can create a GiST index on the timestamp:

create index on a using gist (t) ;

and use the following query:

select *
from a where t in (select t
                  from a
                  order by t <-> timestamp '2019-03-01 17:00:00'
                  limit 1);