Sql Anywhere时区转换函数开发需求：输入日期时间返回指定地点对应时间

Got it, let's build this timezone conversion function tailored to your setup—since your SQL Anywhere database syncs with your local machine's time, we can use the database's built-in timezone handling to make this clean and reliable.

Core Approach #

SQL Anywhere (version 12.0+) supports the AT TIME ZONE syntax, which lets us easily convert datetime values between timezones. Since your database uses the same time as your local machine, we can treat the input datetime as being in the database's current timezone (which matches your local time), then convert it to your target location's timezone.

Function Implementation #

Here's a reusable stored function that takes a datetime input and a target timezone string, then returns the converted datetime:

CREATE FUNCTION ConvertToTargetTimezone(
    @InputDatetime DATETIME,
    @TargetTimezone VARCHAR(100)
)
RETURNS DATETIME
BEGIN
    -- Step 1: Attach the local/database timezone to the input datetime
    DECLARE @LocalWithTZ TIMESTAMP WITH TIME ZONE;
    SET @LocalWithTZ = @InputDatetime AT TIME ZONE CURRENT TIME ZONE;

    -- Step 2: Convert to the target timezone
    DECLARE @TargetWithTZ TIMESTAMP WITH TIME ZONE;
    SET @TargetWithTZ = @LocalWithTZ AT TIME ZONE @TargetTimezone;

    -- Step 3: Convert back to DATETIME type (standard datetime output)
    RETURN CAST(@TargetWithTZ AS DATETIME);
END;

Usage Examples #

Test the function with these common scenarios:

• Convert the current local time to New York time (US Eastern):

  SELECT ConvertToTargetTimezone(CURRENT TIMESTAMP, 'US/Eastern');
  

• Convert a specific datetime to Tokyo time:

  SELECT ConvertToTargetTimezone('2024-05-20 14:30:00', 'Asia/Tokyo');
  

• Convert to UTC:

  SELECT ConvertToTargetTimezone('2024-05-20 14:30:00', 'UTC');
  

Fallback for Older SQL Anywhere Versions #

If you're using a version older than 12.0 (where AT TIME ZONE isn't available), we can calculate timezone offsets manually with TZ_OFFSET:

CREATE FUNCTION ConvertToTargetTimezone_OldVersion(
    @InputDatetime DATETIME,
    @TargetTimezone VARCHAR(100)
)
RETURNS DATETIME
BEGIN
    -- Get offset (in minutes) for local and target timezones
    DECLARE @LocalOffset INT;
    SET @LocalOffset = DATEPART(tz, CURRENT TIMESTAMP WITH TIME ZONE);

    DECLARE @TargetOffset INT;
    SET @TargetOffset = DATEPART(tz, CURRENT TIMESTAMP WITH TIME ZONE AT TIME ZONE @TargetTimezone);

    -- Adjust the input datetime by the offset difference
    RETURN DATEADD(minute, @TargetOffset - @LocalOffset, @InputDatetime);
END;

Key Things to Remember #

• Use full timezone names (like US/Eastern or Asia/Shanghai) instead of abbreviations (like EST). Abbreviations can be ambiguous (e.g., "CST" can mean Central Standard Time or China Standard Time).
• The function assumes your input datetime is in the same timezone as your database/local machine—if you ever need to handle inputs from other timezones, you can modify the function to accept a source timezone parameter too.
• For SQL Anywhere 12.0+, the first function is preferred as it automatically handles daylight saving time changes, whereas the older offset method may need manual adjustments for DST.

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