问询：如何获取带时区日期的偏移小时数及数据库查询实现

Got it, let's break down these two timezone offset tasks clearly:

If you're working in Python, this is straightforward with the standard datetime module (plus pytz for timezone definitions). Here's a quick example:

from datetime import datetime
import pytz

# Example timezone-aware datetime object
tz_aware_dt = datetime(2015, 3, 29, 3, 1, 0, tzinfo=pytz.timezone('Europe/Paris'))

# Calculate offset in hours
offset_hours = tz_aware_dt.utcoffset().total_seconds() // 3600
print(int(offset_hours))  # Outputs: 2

The utcoffset() method returns a timedelta object representing the difference from UTC; dividing by 3600 converts seconds to full hours.

This depends on your database system, since timezone handling varies across SQL implementations. Here are common scenarios:

PostgreSQL #

PostgreSQL has native support for timezone-aware timestamps (TIMESTAMP WITH TIME ZONE). Use the EXTRACT function with TIMEZONE_HOUR:

SELECT
  your_timestamp_column,
  EXTRACT(TIMEZONE_HOUR FROM your_timestamp_column) AS timezone_offset_hours
FROM your_table;

This will return an integer like 2 for +02:00.

MySQL #

If your column stores timestamps as strings in the format YYYY-MM-DD HH:MM:SS ±HH:MM, you can parse the offset with string functions:

SELECT
  your_datetime_string,
  CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(your_datetime_string, ' ', -1), ':', 1) AS SIGNED) AS timezone_offset_hours
FROM your_table;

This works by first grabbing the timezone segment (after the last space), then extracting the part before the colon, and converting it to a signed integer.

SQL Server #

Use DATEPART(tzoffset, ...) which returns the offset in minutes, then divide by 60 to get hours:

SELECT
  your_datetimeoffset_column,
  DATEPART(tzoffset, your_datetimeoffset_column) / 60 AS timezone_offset_hours
FROM your_table;

For a value like 2015-03-29 03:01:00 +02:00, this will return 2.

内容的提问来源于stack exchange，提问作者Dan