How to filter table with timestamp column in postgresql-CodePudding

I have 'stock_price_15m' table which stores 15 minute OHLC candlestick data.

niftydb=# \d stock_price_15m;

                    Table "public.stock_price_15m"
  Column  |           Type           | Collation | Nullable | Default
---------- -------------------------- ----------- ---------- ---------
 stock_id | integer                  |           | not null |
 dt       | timestamp with time zone |           | not null |
 open     | real                     |           | not null |
 high     | real                     |           | not null |
 low      | real                     |           | not null |
 close    | real                     |           | not null |
 volume   | integer                  |           | not null |
Indexes:
    "stock_price_15m_pkey" PRIMARY KEY, btree (stock_id, dt)
    "stock_price_15m_dt_idx" btree (dt DESC)
    "stock_price_15m_stock_id_dt_idx" btree (stock_id, dt DESC)
Foreign-key constraints:
    "fk_stock" FOREIGN KEY (stock_id) REFERENCES stock(id)
Triggers:
    ts_insert_blocker BEFORE INSERT ON stock_price_15m FOR EACH ROW EXECUTE FUNCTION _timescaledb_internal.insert_blocker()
Number of child tables: 51 (Use \d  to list them.)

Here are the samples records :-

niftydb=# SELECT * FROM stock_price_15m WHERE date(dt) = '2022-12-22' AND stock_id = 7 ORDER BY dt;
 stock_id |            dt             |  open  |  high  |  low   | close  | volume
---------- --------------------------- -------- -------- -------- -------- --------
        7 | 2022-12-22 09:15:00 05:30 |  390.2 | 391.95 |  390.1 |  390.1 | 226604
        7 | 2022-12-22 09:15:04 05:30 | 391.05 | 391.05 | 391.05 | 391.05 |      0
        7 | 2022-12-22 09:30:00 05:30 |    390 | 390.65 |  389.2 | 389.95 | 260416
        7 | 2022-12-22 09:45:00 05:30 |  389.8 |  391.6 |  389.7 |  391.1 | 205002
        7 | 2022-12-22 10:00:00 05:30 | 391.25 |  391.3 | 389.55 | 389.75 | 184030
        7 | 2022-12-22 10:15:00 05:30 |  389.6 |  389.9 | 388.55 | 388.75 | 233669
(6 rows)

How do I filter out dt records(timestamp with timezone) with seconds or subsecs data? Basically in above scenario I want to filter out

7 | 2022-12-22 09:15:04 05:30 | 391.05 | 391.05 | 391.05 | 391.05 | 0

I tried converting dt(timestamp) to to_char but not getting the desired result. Appreciate your help!!

CodePudding user response：

Finally able to resolve by the following SQL query:-

SELECT * FROM stock_price_15m WHERE date(dt) = '2022-12-22' AND stock_id = 7 AND extract(second from dt::timestamp) > 0 ORDER BY dt;

likewise you can replace 'second' with 'hour' or 'minute' to filter based on hours or minutes resp.

Reference to stackoverflow article:-

regex timestamp article

Output :-

niftydb=# SELECT * FROM stock_price_15m WHERE date(dt) = '2022-12-22' AND stock_id = 7 AND extract(second from dt::timestamp) > 0 ORDER BY dt;
 stock_id |            dt             |  open  |  high  |  low   | close  | volume
---------- --------------------------- -------- -------- -------- -------- --------
        7 | 2022-12-22 09:15:04 05:30 | 391.05 | 391.05 | 391.05 | 391.05 |      0
(1 row)

CodePudding user response：

We can use EXTRACT to get the seconds from the timestamp and check whether they are <> 0:

SELECT * FROM stock_price_15m 
WHERE date(dt) = '2022-12-22' 
AND EXTRACT(SECOND FROM dt) <> 0
AND stock_id = 7 
ORDER BY dt;

Have a look on the documentation

Sample fiddle based on your data: db<>fiddle