I need to insert rows into PG one of the fields is date and time with time stamp, this is the time of incident, so I can not use --> current_timestamp function of Postgres at the time of insertion, so how can I then insert the time and date which I collected before into pg row in the same format as it would have been created by current_timestamp at that point in time.
Add a comment
|
8 Answers
If you use psycopg2 (and possibly some other client library), you can simply pass a Python datetime object as a parameter to a SQL-query:
from datetime import datetime, timezone
dt = datetime.now(timezone.utc)
cur.execute('INSERT INTO mytable (mycol) VALUES (%s)', (dt,))
(This assumes that the timestamp with time zone type is used on the database side.)
More Python types that can be adapted into SQL (and returned as Python objects when a query is executed) are listed here.
2 Comments
Eugene Yarmash
@Kiro777: Not at all. That would give you a naive datetime object not containing enough information to unambiguously locate itself relative to other datetime objects. The UTC timezone in the code sample is just an example.
A timestamp does not have "a format".
The recommended way to deal with timestamps is to use a PreparedStatement where you just pass a placeholder in the SQL and pass a "real" object through the API of your programming language. As I don't know Python, I don't know if it supports PreparedStatements and how the syntax for that would be.
If you want to put a timestamp literal into your generated SQL, you will need to follow some formatting rules when specifying the value (a literal does have a format).
Ivan's method will work, although I'm not 100% sure if it depends on the configuration of the PostgreSQL server.
A configuration (and language) independent solution to specify a timestamp literal is the ANSI SQL standard:
INSERT INTO some_table
(ts_column)
VALUES
(TIMESTAMP '2011-05-16 15:36:38');
Yes, that's the keyword TIMESTAMP followed by a timestamp formatted in ISO style (the TIMESTAMP keyword defines that format)
The other solution would be to use the to_timestamp() function where you can specify the format of the input literal.
INSERT INTO some_table
(ts_column)
VALUES
(to_timestamp('16-05-2011 15:36:38', 'dd-mm-yyyy hh24:mi:ss'));
3 Comments
Yevgeniy Afanasyev
thanks this ->format('Y-m-d H:i:s'); is working for me.
gies0r
You can also use
ms in to_timestamp to save time millisecond precise.
Ryan Harris
Thanks! Using
TIMESTAMP saved me a whole bunch of time from having to append timezones in Python to all of my datetime values.
4 Comments
anthropomo
Exactly the same, except that it doesn't throw the error.
Eugene Yarmash
The OP explicitly asked how to do this via Python.
schlingel
@eugeney You're right. But it seems that some people found it helpful nevertheless. That's why I kept the answer here.
Date and time input is accepted in almost any reasonable format, including ISO 8601, SQL-compatible, traditional POSTGRES, and others. For some formats, ordering of month, day, and year in date input is ambiguous and there is support for specifying the expected ordering of these fields.
In other words: just write anything and it will work.
Or check this table with all the unambiguous formats.
Comments
Sure, just pass a string value for that timestamp column in the format: '2011-05-16 15:36:38' (you can also append a timezone there, like 'PST'). PostgreSQL will automatically convert the string to a timestamp. See http://www.postgresql.org/docs/9.0/static/datatype-datetime.html#DATATYPE-DATETIME-INPUT
Comments
from datetime import datetime as dt
then use this in your code:
cur.execute('INSERT INTO my_table (dt_col) VALUES (%s)', (dt.now(),))
answered Mar 20, 2015 at 7:19
Mohideen bin Mohammed
20.4k11 gold badges121 silver badges131 bronze badges
Comments
Use strftime for datetime objectives:
x.strftime(r'%Y-%m-%d %H:%M:%S')
The main idea here to transform the the datetime into a Postgres timestamp without timezone, which should be a string when insert.
Comments
Just use
now()
or
CURRENT_TIMESTAMP
I prefer the latter as I like not having additional parenthesis but thats just personal preference.
1 Comment
Mark Hildreth
The question specifically states, "I can not use --> current_timestamp function of Postgres at the time of insertion".