Skip to content

json_agg returns different result for datetime with timezone column #1103

Closed
@zulqasar

Description

@zulqasar

Postgres version 15.4

  • asyncpg version 0.28.0:
  • PostgreSQL version 15.4:
  • Python version 3.12:
  • macOS Monterey:
  • Do you use pgbouncer? NO:
  • Did you install asyncpg with pip? YES:
  • If you built asyncpg locally, which version of Cython did you use?:
  • Can the issue be reproduced under both asyncio and
    uvloop?
    :

timestamp with timezone column gives different result when used with json_agg function

`

    q = """
    CREATE TABLE IF NOT EXiSTS test_tbl
    (
        id bigint primary key generated by default as identity,
        created_at timestamp with time zone
    )
    """

    async def test():
        async with PG() as pg:
            async with pg.acquire() as con:
    
                # create test_tbl
                await con.execute(q)
    
                # insert data
                now = datetime.now(timezone.utc)
                print('now is: ', now)
                id = await con.fetchval(
                    'Insert into test_tbl (created_at) values ($1) returning id',
                    now
                )
                q1 = "Select created_at from test_tbl where id = $1"
                q2 = f"Select json_agg(e) from ({q1}) e"
    
                r1 = await con.fetchval(q1, id)
                r2 = await con.fetchval(q2, id)
                print('result 1: ', r1)
                print('result 2: ', r2)
    
    asyncio.run(test())

`
RESULTS
now is: 2023-11-15 09:31:22.408820+00:00
result 1: 2023-11-15 09:31:22.408820+00:00
result 2: [{"created_at":"2023-11-15T03:31:22.40882-06:00"}]

row in table
| id | created_at |
| 1 | 2023-11-15 09:31:22.408820 +00:00 |

Executing in postgres
Select json_agg(e) from (select created_at from test_tbl where id = 1) e
Result >>
[{"created_at":"2023-11-15T09:31:22.40882+00:00"}]

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions