Datetime issues when Postgres database has a timezone set.

[djpiper28]

  File "/home/bracket/.local/share/virtualenvs/app-4PlAip0Q/lib/python3.10/site-packages/asyncpg/connection.py", line 1945, in _do_execute
    result = await executor(stmt, None)
  File "asyncpg/protocol/protocol.pyx", line 207, in bind_execute
    return await waiter
  File "asyncpg/protocol/coreproto.pyx", line 70, in asyncpg.protocol.protocol.CoreProtocol._read_server_messages
    self._process__bind_execute(mtype)
  File "asyncpg/protocol/coreproto.pyx", line 225, in asyncpg.protocol.protocol.CoreProtocol._process__bind_execute
    self._parse_data_msgs()
  File "asyncpg/protocol/coreproto.pyx", line 518, in asyncpg.protocol.protocol.CoreProtocol._parse_data_msgs
    row = decoder(self, cbuf, cbuf_len)
  File "asyncpg/protocol/protocol.pyx", line 839, in asyncpg.protocol.protocol.BaseProtocol._decode_row
    return self.statement._decode_row(buf, buf_len)
  File "asyncpg/protocol/prepared_stmt.pyx", line 328, in asyncpg.protocol.protocol.PreparedStatementState._decode_row
    val = codec.decode(settings, &rbuf)
  File "asyncpg/protocol/codecs/base.pyx", line 330, in asyncpg.protocol.protocol.Codec.decode
    return self.decoder(self, settings, buf)
  File "asyncpg/protocol/codecs/base.pyx", line 327, in asyncpg.protocol.protocol.Codec.decode_in_python
    return self.py_decoder(data)
  File "/app/bracket/database.py", line 12, in datetime_decoder
    return datetime_utc.fromisoformat(value)
  File "/home/bracket/.local/share/virtualenvs/app-4PlAip0Q/lib/python3.10/site-packages/heliclockter/__init__.py", line 249, in fromisoformat
    return cls.from_datetime(_datetime.datetime.fromisoformat(date_string))
ValueError: Invalid isoformat string: '2024-04-29 22:01:25+01'

I plan on fixing this by changing the datetime_utc to datetime_tz in the model, this will allow my Europe/London database to work with the software.

[evroon]

Thanks for opening the issue.

What is the advantage of changing datetime_utc to datetime_tz? And why not just set your database's timezone to UTC?

I don't think changing datetime_utc to datetime_tz is a good idea, because it adds complexity and hides which timezone is actually used. Bracket already writes in the same timezone as it reads (UTC), so there shouldn't be a reason to use something other than UTC. In the end, postgres uses UTC internally anyway. If there really would be a usecase, it would be better to convert timezones when reading/writing to the database.

[djpiper28]

My database is set to my local timezone just because it is. I think setting to UTC is probably the easiest fix, especially when it comes to setting timezones in docker.

[djpiper28]

I have my datetimes stored in Europe/London still, maybe the schema should be timestamp instead of timestamptz? Although I am not sure how that would be set with the ORM.

Edit: only set it for the session, after changing the config it works now.