I have a date string 2020-10-14, 14:13:23, I want to convert this to timestamp with timezone format in python so that I can delete the rows with this particular timestamp in my postgresql table
Please suggest how to proceed?
Add a comment
|
1 Answer
Do you want to cast?
select '2020-10-14, 14:13:23'::timestamp with time zone
| timestamptz | | :--------------------- | | 2020-10-14 14:13:23+01 |
If you want to filter on timestamps that belong to the same second as this literal value:
select *
from mytable
where execution_date >= '2020-10-14, 14:13:23'::timestamp with time zone
and execution_date < '2020-10-14, 14:13:23'::timestamp with time zone + interval '1 second'
4 Comments
Mahesh
I did
select '2020-10-14, 14:13:23'::timestamp with time zone from table_name and I got 16 rows but in actual the table contains only two rows with this timestamp. Am i doing something wrong?
GMB
@Mahesh: please provide sample data and desired results. Better yet, are you able to set up a dbfiddle.uk that demonstrates the issue?
Mahesh
hey, I am unable to provide sample data in the fiddle never used it.. but what I am trying to do is fetch all the rows with this timestamp. so i wrote the query
select * from table_name where execution_date in (select '2020-10-14, 14:13:23'::timestamp with time zone) but it is returning 0 rows. execution_date is the column name as shown in the above imageGMB
@Mahesh: your data has fractional seconds, not the value that you are passing. So there is no exact match.