Changing Time Type for Postgres In Python

Question

I have times from a table that take this form:

2017-12-31T23:59:59.8560818Z

I'm not entirely sure what form this actually is. All I know is that I want to convert it in python to a type of time that is acceptable to insert into a postgres table as a timestamp or date (I really only care about the date). Any ideas for a better way to do this than just taking a substring of the date?

When I try this:

exchange_rate['time'] = str(exchange_rate['time'])[:10]  #captures the YYY-MM-DD from date
exchange_rate['time'] = datetime.datetime.strptime(exchange_rate['time'], '%Y-%m-%d') #converts to datetime

cursor.execute('''INSERT INTO bitcoin VALUES ({},{})'''.format(exchange_rate['time'],exchange_rate['rate']))

I get this error:

Date literals in postgresql must be quoted. (with single quotes) — wildplasser
– wildplasser, Commented Oct 19, 2019 at 12:44

wildplasser · Accepted Answer · 2019-10-19 12:58:54Z

1

Date-literals should be quotes (otherwise the tokenizer would get confused...)
if you want to chop off the fractional seconds, date_trunc() could be used.

CREATE TABLE bogus
        ( id integer PRIMARY KEY
        , ztimestamp TIMESTAMP WITH TIME ZONE NOT NULL
        );
INSERT INTO bogus(id,ztimestamp) VALUES
 ( 1, '2017-12-31T23:59:59.8560818Z')
,( 2, DATE_TRUNC('sec', '2017-12-31T23:59:59.8560818Z'::timestamp))
        ;

SELECT * FROM bogus;

RESULT:

CREATE TABLE
INSERT 0 2
 id |          ztimestamp           
----+-------------------------------
  1 | 2018-01-01 00:59:59.856082+01
  2 | 2017-12-31 23:59:59+01
(2 rows)

answered Oct 19, 2019 at 12:58

wildplasser

44.5k9 gold badges72 silver badges116 bronze badges

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

Collectives™ on Stack Overflow

Changing Time Type for Postgres In Python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related