SQL Timestamp in PostgreSQL

Question

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?

The records apparently uses its own timestamp format which differs from the default Postgres format. But both string represent the same timestamp. — clemens
– clemens, Commented Nov 29, 2017 at 6:53

Vao Tsun · Accepted Answer · 2017-11-29 09:13:18Z

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)

Collectives™ on Stack Overflow

SQL Timestamp in PostgreSQL

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