PostgreSQL filter by timestamp

Question

I have a table named report that looks like following;

enter image description here

which are the types of,

enter image description here

Here I'm trying to change the rows filtering by order_id and start_picking_hour. There is no problem with order_id. However, I cannot achieve anything when I filter by start_picking_hour.

For example, the following code works, and SQL Manager says that "2 rows affected";

UPDATE report SET picked_count = 10 WHERE order_id = 168366

However, I'm trying to filter by both order_id and start_picking_hour. I am not sure about how to filter by a type of timestamp. None of the following queries I tried worked. Each one returned a message of "0 rows affected".

UPDATE report SET picked_count = 10 WHERE order_id = 168366 and
      start_picking_hour = TO_TIMESTAMP('2/17/2015 10:12:51 AM','dd-mm-yyyy hh12:mi:ss')

UPDATE report SET picked_count = 10 WHERE order_id = 168366 and
      start_picking_hour = TO_TIMESTAMP('2/17/2015 10:12:51','dd-mm-yyyy hh12:mi:ss')

UPDATE report SET picked_count = 10 WHERE order_id = 168366 and
      start_picking_hour = TO_TIMESTAMP('2/17/2015 10:12:51 AM','dd-mm-yyyy hh:mi:ss')

UPDATE report SET picked_count = 10 WHERE order_id = 168366 and
      start_picking_hour = TO_TIMESTAMP('2/17/2015 10:12:51','dd-mm-yyyy hh:mi:ss')

UPDATE report SET picked_count = 10 WHERE order_id = 168366 and
      start_picking_hour = '2/17/2015 10:12:51 AM'

UPDATE report SET picked_count = 10 WHERE order_id = 168366 and
      start_picking_hour = 2/17/2015 10:12:51 AM

UPDATE report SET picked_count = 10 WHERE order_id = 168366 and
      start_picking_hour = '2/17/2015 10:12:51'

UPDATE report SET picked_count = 10 WHERE order_id = 168366 and
      start_picking_hour = 2/17/2015 10:12:51

What is the exact way of filtering by timestamp in a WHERE clause? I am using PostgreSQL.

What do you get with this query: SELECT * FROM report WHERE start_picking_hour = TO_TIMESTAMP('2/17/2015 10:12:51 AM','dd-mm-yyyy hh12:mi:ss'); — Frank Heikens
– Frank Heikens, Commented Feb 17, 2015 at 12:21
A timestamp also contains fractional seconds (which your timestamp literal does not have). Not sure if your SQL client displays them, but you could try: date_trunc('second', start_picking_hour) = timestamp '2015-02-17 10:12:51' that will get rid of milliseconds in the start_picking_hour column. — user330315
– user330315, Commented Feb 17, 2015 at 12:29
@a_horse_with_no_name This one worked! Thanks very much. Write answer if you wish so that it could be read better. — Faruk Yazici
– Faruk Yazici, Commented Feb 17, 2015 at 12:45

user330315 · Accepted Answer · 2015-02-17 12:54:31Z

5

A timestamp also contains fractional seconds (milliseconds). A timestamp literal like the one you are using (TO_TIMESTAMP('2/17/2015 10:12:51 AM','dd-mm-yyyy hh12:mi:ss')) does not contain milliseconds.

You can use the date_trunc() function to "remove" the milliseconds from the timestamp values (they are not "removed" - just set to zero):

where date_trunc('second', start_picking_hour) = timestamp '2015-02-17 10:12:51'

^{(I prefer the ANSI timestamp literals over to_timestamp() because they are shorter to write, portable and unambiguous)}

Note that this will prevent the usage of an index on the start_picking_hour column.

If you don't care about the milliseconds, you can define your column as timestamp(0) instead. In that case milliseconds will never be stored and you can compare the column directly to a timestamp literal (that does not contain milliseconds). In that case an index on that column can be used.

answered Feb 17, 2015 at 12:54

user330315

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

1 Comment

Frank Heikens Over a year ago

You could also use BETWEEN: start_picking_hour BETWEEN TO_TIMESTAMP('2/17/2015 10:12:51 AM','dd-mm-yyyy hh12:mi:ss') AND TO_TIMESTAMP('2/17/2015 10:12:52 AM','dd-mm-yyyy hh12:mi:ss')

Collectives™ on Stack Overflow

PostgreSQL filter by timestamp

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related