How to filter table with timestamp column in postgresql

Question

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!!

Jonas Metzler · Accepted Answer · 2022-12-24 06:22:46Z

1

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

edited Dec 24, 2022 at 6:22

answered Dec 24, 2022 at 6:15

Jonas Metzler

6,8363 gold badges9 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sandeep Kulkarni · Accepted Answer · 2022-12-24 06:17:33Z

0

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)

edited Dec 24, 2022 at 6:17

answered Dec 24, 2022 at 6:16

Sandeep Kulkarni

11 bronze badge

1 Comment

Adrian Klaver Over a year ago

This is the opposite of what you requested in your question. There you wanted to filter out this value and leave only those values that had second values = 0.

Collectives™ on Stack Overflow

How to filter table with timestamp column in postgresql

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related