如何将UTC格式的日期时间字段转换为IST格式（附SQL查询示例）

根据你使用的数据库类型，以下是对应的修改后查询语句：

MySQL/MariaDB #

IST比UTC快5小时30分钟，可直接用CONVERT_TZ函数转换：

SELECT 
    file_name,
    CONVERT_TZ(process_start_time, '+00:00', '+05:30') AS process_start_time_ist,
    CONVERT_TZ(process_end_time, '+00:00', '+05:30') AS process_end_time_ist
FROM Process_logs;

若CONVERT_TZ函数无法正常使用，也可手动偏移时间：

SELECT 
    file_name,
    DATE_ADD(process_start_time, INTERVAL '5:30' HOUR_MINUTE) AS process_start_time_ist,
    DATE_ADD(process_end_time, INTERVAL '5:30' HOUR_MINUTE) AS process_end_time_ist
FROM Process_logs;

PostgreSQL #

使用AT TIME ZONE语法，通过时区名称精准转换：

SELECT 
    file_name,
    process_start_time AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata' AS process_start_time_ist,
    process_end_time AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata' AS process_end_time_ist
FROM Process_logs;

也可直接对时间做固定偏移：

SELECT 
    file_name,
    process_start_time + INTERVAL '5 hours 30 minutes' AS process_start_time_ist,
    process_end_time + INTERVAL '5 hours 30 minutes' AS process_end_time_ist
FROM Process_logs;

SQL Server #

使用SWITCHOFFSET函数转换时区偏移：

SELECT 
    file_name,
    SWITCHOFFSET(process_start_time, '+05:30') AS process_start_time_ist,
    SWITCHOFFSET(process_end_time, '+05:30') AS process_end_time_ist
FROM Process_logs;

如果字段是datetime类型（非datetimeoffset），需先转换类型再调整时区：

SELECT 
    file_name,
    SWITCHOFFSET(CAST(process_start_time AS datetimeoffset), '+05:30') AS process_start_time_ist,
    SWITCHOFFSET(CAST(process_end_time AS datetimeoffset), '+05:30') AS process_end_time_ist
FROM Process_logs;

Oracle #

通过FROM_TZ绑定原时区，再用AT TIME ZONE转换目标时区：

SELECT 
    file_name,
    FROM_TZ(process_start_time, 'UTC') AT TIME ZONE 'Asia/Kolkata' AS process_start_time_ist,
    FROM_TZ(process_end_time, 'UTC') AT TIME ZONE 'Asia/Kolkata' AS process_end_time_ist
FROM Process_logs;

也可直接做时间偏移：

SELECT 
    file_name,
    process_start_time + INTERVAL '05:30' HOUR TO MINUTE AS process_start_time_ist,
    process_end_time + INTERVAL '05:30' HOUR TO MINUTE AS process_end_time_ist
FROM Process_logs;

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