2

First question on here, so let me know if more information is needed. I am using the Python psycopg2-binary==2.7.7 package in an attempt to pull PostgreSQL 9.6.11 timestamptz fields out of a database.

With that said, the 'psycopg2' package seems to be coercing the timestamptz date-times to a different timezone than is present in the database.

For instance, the following query will return the correct offset if run in a PostgreSQL client:

SQL

SELECT row_to_json(t)
FROM (
    SELECT '2019-01-24T08:24:00-05:00'::timestamptz AS tz 
)t;

Result

{"tz":"2019-01-24 08:24:00-05"}

However, if I run the same query via the psycopg2.cursor.fetchall method, I get a different offset than expected/returned: 

import time
import psycopg2
import logging

logger = logging.getLogger()


def getRows(query, printRows=False, **kwargs):
    try:
        cs = "dbname={dbname} user={dbuser} password={dbpass} host={server} port={port}".format(
            **kwargs)
        con = psycopg2.connect(cs)
        con.set_session(readonly=True, autocommit=True)
    except Exception:
        logger.exception("-->>>>Something went wrong connecting to db")
        return None

    end = None

    try:
        start = time.time()
        cur = con.cursor()
        cur.execute(query)

        rows = cur.fetchall()
        if printRows:
            for i in rows:
                print(i)

        cur.close()
        con.commit()
        con.close()
        end = time.time()
        logger.info(
            "-->>>>Query took {} seconds...".format(round(end - start, 2)))
        return rows
    except Exception:
        end = time.time()
        cur.close()
        con.commit()
        con.close()
        logger.exception("-->>>>Something went wrong with the query...")
        logger.info(
            "-->>>>Query took {} seconds...".format(round(end - start, 2)))


if __name__ == '__main__':
    test = getRows("""SELECT row_to_json(t) AS "result" 
                      FROM(
                      SELECT '2019-01-24T08:24:00-05:00'::timestamptz AS tz
                      )t;
                   """, printRows=True, **DBSECRETS)
    print(test[0][0])

Result

{'tz': '2019-01-24T05:24:00-08:00'}

As seen above, the EST timezone (offset of -5)to PostgreSQL is being converted to a -08:00 offset via the psycopg2 package.

I've checked the psycopg2 documentation but could not find any conclusive examples to fix this issue. Specifically, I've checked here:

http://initd.org/psycopg/docs/cursor.html#cursor.tzinfo_factory

Jonathan Hall
  • 75,165
  • 16
  • 143
  • 189
dmarkha1
  • 41
  • 5

1 Answers1

1

It turns out that the SQL Client, Dbeaver, coerces a timestamptz to the local OS timezone, which in this case is EST.

How to change DBeaver timezone / How to stop DBeaver from converting date and time

The PostgreSQL server, however, has a native timezone of Pacific time or PST. Thus, the psycopg2 package was interpreting the timestamptz correctly according to the server, i.e. PST.

dmarkha1
  • 41
  • 5