Python date string to postgres usable date

Question

Issue: I stored some dates as text in my Postgres table, and I want to convert it over to actual dates, again in Postgres.

Im not sure if there is a better way to do this or what im doing wrong. I have pulled a bunch of data into a PostgreSQL database in just text format. As a result I need to go back through and clean it up. I am running into issues with the data. I need to convert it into a format that PostgreSQL can use. I went about pulling it back into python and trying to convert and kick it back. Is this the best way to do this? Also I am having issue with datetime.strptime.. I believe i've got the directive correct but no go. :/

import psycopg2
from datetime import datetime

# connect to the PostgreSQL database
conn = psycopg2.connect(
"dbname='postgres' user='postgres' host=10.0.75.1 password='mysecretpassword'")
# create a new cursor
cur = conn.cursor()
cur.execute("""SELECT "Hash","Date" FROM nas """)
# commit the changes to the database
myDate = cur.fetchall()

for rows in myDate:
    target = rows[1]

datetime.strptime(target, '%B %d, %Y, %H:%M:%S %p %Z')

I'm confused. If you're selecting dates from Postgres, then where does the issue of Postgres not being able to use your data come into play? — Tim Biegeleisen
– Tim Biegeleisen, Commented Sep 14, 2017 at 2:04
Sorry for the confusion. Postgres sees them as a text data type. On trying to convert this over to a correct dateZ type it errors due to it not being in the correct format. — Mal229
– Mal229, Commented Sep 14, 2017 at 2:05
I still don't see where Postgres comes into play here. You are selecting data out of Postgres, not the other way around. Why does the format of dates in your Python script have anything to do with Postgres? — Tim Biegeleisen
– Tim Biegeleisen, Commented Sep 14, 2017 at 2:07
The end goal is to have the correctly formatted data structure 'YYYY-MM-DD HH:MM:SS-TZ' in postgres. My approach was to pull that date row into python format it and update the table. — Mal229
– Mal229, Commented Sep 14, 2017 at 2:09
Wait...now the real issue is finally coming out. So are you saying that your stored some dates as text in your Postgres table, and you want to convert it over to actual dates, again in Postgres? — Tim Biegeleisen
– Tim Biegeleisen, Commented Sep 14, 2017 at 2:10

Tim Biegeleisen · Accepted Answer · 2017-09-14 02:33:55Z

1

Here is a Postgres query which can convert your strings into actual timestamps:

select
    ts_col,
    to_timestamp(ts_col, 'Month DD, YYYY HH:MI:SS PM')::timestamp with time zone
from your_table;

For a full solution, you might take the following steps:

create a new timestamp column ts_col_new in your table
update that column using the logic from the above query
then delete the old column containing text

The update might look something like this:

update your_table
set ts_col_new = to_timestamp(ts_col, 'Month DD, YYYY HH:MI:SS PM')::timestamp with time zone;

edited Sep 14, 2017 at 2:33

answered Sep 14, 2017 at 2:19

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

1 Comment

Mal229 Over a year ago

Thanks tim! Yeah once I dropped the TZ it was happy. Thanks again for all your help.

Collectives™ on Stack Overflow

Python date string to postgres usable date

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