Home Python date string to postgres usable date
Reply: 1

Python date string to postgres usable date

Mal229
1#
Mal229 Published in 2017-09-14 02:01:10Z

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')

Tim Biegeleisen
2#
Tim Biegeleisen Reply to 2017-09-14 02:33:55Z

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;
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.441277 second(s) , Gzip On .

© 2016 Powered by cudou.com design MATCHINFO