CODEWITHSUNDEEP
pythonsqlpostgresqlpsqldatetime-format
qarthandso
qarthandso

Reputation: 2188

SQL Timestamp in PostgreSQL

I'm trying to understand the raw manner in which PostgreSQL saves timestamp data types. I get 2 different results depending on the client I use:

1. psql

# SELECT date_incorporated FROM client;

   date_incorporated    
------------------------
 2017-06-14 19:42:15-04

2. records python module

rows = db.query('SELECT date_incorporated FROM client')
print(rows[0])
# {"date_incorporated": "2017-06-14T19:42:15-04:00"}

Since the psql interface and records module are both supposed to be giving me back the raw data, I can't understand why both are giving me back different formats of the timestamp they have stored.

The two differences I see so far are the T's in the middle between the date and time in the records version, and also the differing ways in which it shows the time zone at the end of the string

Is one of them altering it? Which one is showing the real data?

Upvotes: 2

Views: 1076

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51629

https://www.postgresql.org/docs/current/static/datatype-datetime.html

All timezone-aware dates and times are stored internally in UTC. They are converted to local time in the zone specified by the TimeZone configuration parameter before being displayed to the client.

https://www.postgresql.org/docs/current/static/datatype-datetime.html#DATATYPE-DATETIME-OUTPUT

The output format of the date/time types can be set to one of the four styles ISO 8601, SQL (Ingres), traditional POSTGRES (Unix date format), or German. The default is the ISO format.

EG:

t=# select now();
              now
-------------------------------
 2017-11-29 09:07:31.716276+00
(1 row)

t=# set datestyle to SQL;
SET
t=# select now();
              now
--------------------------------
 11/29/2017 09:07:52.341416 UTC
(1 row)

so the time is saved not the way it is returned. at least not neseserely. You can control up to some level how it it returned to your client. psql does not process time. but python does. not records I believe but python itself

https://en.wikipedia.org/wiki/ISO_8601

T is the time designator that precedes the time components of the representation.

And that T is definetely not added by postgres itself (unless you deliberately format the date with to_char)

Upvotes: 1

Related Questions