How to store the time offset given in a timestamptz literal?

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.

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.