sql - How to store the time offset given in a timestamptz literal? - Stack Overflow

admin管理员组

文章数量:1122832

I need to store the the given time zone in the column (operation_time_zone):

INSERT INTO segments (
    operation_type, operation_time, operation_time_zone, operation_place, 
    passenger_name, passenger_surname, passenger_patronymic, 
    doc_type, doc_number, birthdate, gender, passenger_type, 
    ticket_number, ticket_type, airline_code, flight_num, 
    depart_place, depart_datetime, arrive_place, arrive_datetime, 
    pnr_id, serial_number) 
VALUES (
    $1, 
    ($2 AT TIME ZONE 'UTC')::TIMESTAMP,
    (EXTRACT(TIMEZONE FROM $2::TIMESTAMPTZ) / 3600)::SMALLINT,
    $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21
);

But passed this way I get 0 in operation_time_zone.
Value $2 is '2022-01-01T03:25:00+03:00'. If I execute this query:

SELECT (EXTRACT(TIMEZONE FROM '2022-01-01T03:25:00+03:00'::TIMESTAMPTZ) / 3600)::SMALLINT AS timezone_in_hours;

I get 3.

I need to store the the given time zone in the column (operation_time_zone):

INSERT INTO segments (
    operation_type, operation_time, operation_time_zone, operation_place, 
    passenger_name, passenger_surname, passenger_patronymic, 
    doc_type, doc_number, birthdate, gender, passenger_type, 
    ticket_number, ticket_type, airline_code, flight_num, 
    depart_place, depart_datetime, arrive_place, arrive_datetime, 
    pnr_id, serial_number) 
VALUES (
    $1, 
    ($2 AT TIME ZONE 'UTC')::TIMESTAMP,
    (EXTRACT(TIMEZONE FROM $2::TIMESTAMPTZ) / 3600)::SMALLINT,
    $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21
);

But passed this way I get 0 in operation_time_zone.
Value $2 is '2022-01-01T03:25:00+03:00'. If I execute this query:

SELECT (EXTRACT(TIMEZONE FROM '2022-01-01T03:25:00+03:00'::TIMESTAMPTZ) / 3600)::SMALLINT AS timezone_in_hours;

I get 3.

• sql
• database
• postgresql
• timezone
• extract

Share Improve this question Follow edited Nov 24, 2024 at 19:46 Erwin Brandstetter 654k156156 gold badges1.1k1.1k silver badges1.3k1.3k bronze badges asked Nov 23, 2024 at 16:02 user11225404user11225404 13911 silver badge99 bronze badges 6

• 1 1) What is the TimeZone set in the server? 2) What client are you using to run the query? 3) select 1000/3600; 0. Postgres does integer division unless you coerce one of the values to a non-integer type select 1000/3600.0; 0.27777777777777777778. – Adrian Klaver Commented Nov 23, 2024 at 16:28
• @AdrianKlaver 1) GMT +3 hours 2)github.com/jackc/pgx/v4 for golang – user11225404 Commented Nov 23, 2024 at 16:43
• What is the value when you do show timezone;? – Adrian Klaver Commented Nov 23, 2024 at 17:05
• $1 etc implies you are injecting data directly from your client code, which is a really bad idea. How are you actually passing the parameters into the SQL statement? – Charlieface Commented Nov 23, 2024 at 19:22
• @Charlieface , i passed this way _, err := tx.ExecContext(ctx, query, segment.OperationType, segment.OperationTime, segment.OperationPlace, segment.PassengerName, segment.PassengerSurname, segment.PassengerPatronymic, segment.DocType, segment.DocNumber, segment.Birthdate, segment.Gender, segment.PassengerType, segment.TicketNumber, segment.TicketType, segment.AirlineCode, segment.FlightNum, segment.DepartPlace, segment.DepartDatetime, segment.ArrivePlace, segment.ArriveDatetime, segment.PNRID, i+1, ) query is my upper code. tx is a transaction. Golang. – user11225404 Commented Nov 24, 2024 at 8:00

 |  Show 1 more comment

1 Answer 1

Sorted by: Reset to default 2

Explanation

This expression only reflects the timezone setting of your current session:

EXTRACT(TIMEZONE FROM $2::TIMESTAMPTZ)

If you get 0, your session runs on UTC time (or a related time zone with no offset to Greenwich at the given time).
If you get 3, your session is set to a timezone somewhere near Russia - Turkey - Arabia - East-Africa. It only happens to coincide with the offset in the literal '2022-01-01T03:25:00+03:00'.

fiddle

Postgres timestamp data types do not store any time zone information at all - neither timestamp with time zone (timestamptz) nor timestamp without time zone (timestamp). The display of a timestamptz value is adjusted to the timezone setting of the current session, i.e. EXTRACT always sees the same offset.

See:

• Time zone storage in data type "timestamp with time zone"
• Ignoring time zones for Postgres timestamps

Solution

To preserve the time offset given in a timestamptz literal, pass $2 as text, and either parse the text representation (which is very tricky!) or, rather, use this expression:

   $2::timestamp AT TIME ZONE 'UTC' - $2::timestamptz

$2::timestamp casts the timestamptz literal to timestamp, ignoring the time offset (which is typically a sneaky mistake, but intended this time). Subtracting a properly cast timestamptz produces the effective time offset in the literal. This is cheaper than string processing and works for time zone names or abbreviations, too. Consider this comprehensive demo:

fiddle

Your INSERT could work like this:

INSERT INTO segments (
   ...
 , operation_time
 , operation_time_zone
 , ...
   )
VALUES (
   ...
 , $2::timestamptz AT TIME ZONE 'UTC'
 , EXTRACT(hour FROM $2::timestamp AT TIME ZONE 'UTC' - $2::timestamptz)::int2
 , ...
   )

I also simplified to extract hours directly. Your integer division would truncate to the hour anyway.

本文标签： sqlHow to store the time offset given in a timestamptz literalStack Overflow