CODEWITHSUNDEEP
sqldatabasepostgresqltimestamp
Faruk Yazici
Faruk Yazici

Reputation: 2404

PostgreSQL filter by timestamp

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.

Upvotes: 6

Views: 7511

Answers (1)

user330315
user330315

Reputation:

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.

Upvotes: 5

Related Questions