Is there a way to specify a time zone when scanning postgres timestamp without time zone fields?

[timuckun]

To help with debugging please provide your code with all relevant information to scany. I have a query which selects one row from the database. This row has date fields in it. When the dates are scanned they end up as UTC dates

I would like to be able to specify that the date is in fact in a different time zone. I don't want to convert the date into my time zone because the date is already in my time zone. I just want to set the date to that time zone.

Thanks.

[georgysavva]

Hey. Are you able to achieve what you want without scany? I mean, scanning manually via your database library? I am asking because it seems like the problem you are trying to solve isn't related to scany. It's purely about database and database library integration.

[timuckun]

I am not sure where the problem is exactly. In order to narrow it down here is what I did.

I created a test table

drop table if exists tztest;
CREATE TABLE IF NOT EXISTS public.tztest
(
    tz timestamp with time zone,
    notz timestamp without time zone
);

Insert into public.tztest (tz, notz) values
        ('2021-10-09 21:04' at time zone 'Pacific/Auckland','2021-10-09 21:04' at time zone 'Pacific/Auckland' );

I wrote a select statement

select
    tz as with_tz
    ,notz as without_tz
    ,tz at time zone 'Pacific/Auckland' as with_tz_in_auckland
    ,notz  at time zone 'Pacific/Auckland' as without_tz_in_auckland
    ,tz at time zone 'UTC' as with_tz_in_utc
    ,notz at time zone 'UTC' as without_tz_in_utc
   from public.tztest

I created a struct

type Tztest struct {
    With_tz                time.Time
    Without_tz             time.Time
    With_tz_in_auckland    time.Time
    Without_tz_in_auckland time.Time
    With_tz_in_utc         time.Time
    Without_tz_in_utc      time.Time
}

I then used pgxscan.Get to fill this struct.

Here is what I got

main.Tztest{
  With_tz:                2021-10-09 21:04:00 Local,
  Without_tz:             2021-10-09 21:04:00 UTC,
  With_tz_in_auckland:    2021-10-09 21:04:00 UTC,
  Without_tz_in_auckland: 2021-10-09 21:04:00 Local,
  With_tz_in_utc:         2021-10-09 08:04:00 UTC,
  Without_tz_in_utc:      2021-10-10 10:04:00 Local,
}

My connection string specifies Pacific/Auckland

So timestamp with TZ returns the correct time with the proper locale set. Postgres knows I am in Auckland and gives me the converted time (it stores the data as UTC)

Timestamp without TZ is not stored as UTC, and postgres makes no effort to convert it to anything. But scany reports the data as being in UTC. I am not sure if this is because of PG or scany but I suspect it's scany because if I cast it in the SQL statement to my time zone it comes back with the time zone attached by no conversion is made

The super odd case what happens if I select the without timezone as UTC, scany reports my local time but the time has been converted to UTC.

Much strangeness.

[georgysavva]

Sorry for the very late reply, is there still a problem?