python psycopg2 select current_timestamp problem with timezone

Question

I'm calling a simple select to obtain current timestamp with timezone with psycopg2 and it's retrieving UTC time instead of my local time (-3).

datetime.datetime(2021, 1, 13, 20, 49, 47, 931834, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))

In postgresql I'm doing:

select current_timestamp

This retrieves (Argentina time -3):

2021-01-13 17:39:57

So this is correct, but in Python:

class DatabaseUtils():
    def __init__(self):
        self.dsn = "dbname=my_database user=postgres host=127.0.0.1"
        self.conn, self.cur = self.connect_db()
        self.database_name = "my_table"

    def connect_db(self):
        """
        :param DSN: data source name. ex: "dbname=sigbase user=postgres"
        :return: connection, cursor   < If successful
        """
        try:
            # Connect to the database
            conn = psycopg2.connect(self.dsn)
            # Default encoding is UTF8
            conn.set_client_encoding('UTF8')
            cur = conn.cursor()
        except:
            logger.error(f'Could not connect to database {self.dsn}')
            conn, cur = None, None
        return conn, cur

    def myselect(self):

        query = "select current_timestamp ;"
        self.cur.execute(query)
        records = self.cur.fetchall()
        logger.debug(f"Selected records {records}")

Select method retrieves:

Selected records [(datetime.datetime(2021, 1, 13, 20, 49, 47, 931834, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)),)]

So datetime object is comming with offset 0, i.e UTC. Is it possible to retrieve current timestamp in psycopg2 with the proper timezone? If not, how can I convert the datetime object timezone?

user_1732969 · Answer

I solved this issue in this way: I added a method in the class init to set the timezone. In this way, the SELECT statements are giving the proper time.

def set_timezone(self):
    # Get current time zone.
    timezone = datetime.datetime.now(datetime.timezone.utc).astimezone().tzname()
    # Set timezone.
    self.cur.execute(f"SET TIME ZONE {timezone};")

Result logged in python is:

Selected records [(datetime.datetime(2021, 1, 14, 14, 21, 18, 455322, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=-180, name=None)),)]

This is correct (Argentina time now).

Extra info: I based on the psycopg documentation, in the example is telling the timezone first, before the query. I think the select current_time in this library is working in UTC by default.

Source: https://www.psycopg.org/docs/usage.html#time-zones-handling

python psycopg2 select current_timestamp problem with timezone

1 Answers