Win 环境与Linux 环境基于 PG 库将本地时区时间转换为 UTC 时区时间案例

构造数据

drop table if exists test cascade;
create  table test(id serial primary key, time_info timestamp);

-- 假设下面两条时间戳是业务上的传入的,而且是系统本地时区的时间(比如:北京时间, 北京时间相对于 UTC 时区时间, 相差 8 小时, 需要减去 8 小时 才等于 UTC 时区时间)
insert into test (time_info) values('2024-8-28 09:57:10'),('2024-8-28 10:57:13');
select * from test;
show TimeZone;

查看数据

postgres=# select * from test;
 id |      time_info
----+---------------------
  1 | 2024-08-28 09:57:10
  2 | 2024-08-28 10:57:13
(2 rows)


postgres=#
postgres=#
postgres=# show TimeZone;
 TimeZone
----------
 UTC
(1 row)

编写函数

--将本地时区时间转换为 UTC 时区时间
vim modify_local_timestmap_2_utc_based_timestamp.sql
CREATE OR REPLACE FUNCTION func_modify_local_timestmap_2_utc_based_timestamp(delta_time varchar)
RETURNS  int 
AS
$BODY$
declare

BEGIN
    update test set time_info=time_info-delta_time::interval;
    RETURN 0;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

-- 调用函数, 参数值由 psql 命令行传入
select * from func_modify_local_timestmap_2_utc_based_timestamp(:DELTA_TIME);
EOF

windows 环境

vim modify_local_timestmap_2_utc_based_timestamp.ps1

param(
    [string]$param1,
    [string]$param2,
    [string]$param3,
    [string]$param4,
    [string]$param5
)

$PGSQL=$param1
$PGPORT=$param2
$SQL_FILE_PATH=$param3
$PGDATABASE=$param4
$LOG_FILE=$param5

# 获取本地时区的UTC偏移量
$localTimeZone = [TimeZoneInfo]::Local
$utcOffset = $localTimeZone.BaseUtcOffset
$delta_time = $utcOffset.ToString()


# 注意：参数值需要被转义和以特定方式引用，以便在psql中正确解析
$psqlCommand = "$PGSQL -U postgres -d $PGDATABASE -p $PGPORT -v DELTA_TIME=""'$delta_time'"" -U postgres -f $SQL_FILE_PATH"

# 追加输出到日志文件
$LOG_TIME = Get-Date -Format "yyyy-MM-dd HH:mm:ss"

# 或者直接追加日期和时间信息
"$LOG_TIME - Command output: $($psqlCommand)" | Out-File -FilePath $LOG_FILE -Append -Encoding UTF8

# 执行psql命令
# & cmd /C "$psqlCommand" | Out-File -FilePath $LOG_FILE -Append -Encoding UTF8
"$LOG_TIME" | Out-File -FilePath $LOG_FILE -Append -Encoding UTF8
& cmd /C "$psqlCommand >> $LOG_FILE 2>&1"

# 或者直接追加日期和时间信息
"$LOG_TIME" | Out-File -FilePath $LOG_FILE -Append -Encoding UTF8





vim modify_local_timestamp_to_utc_timestamp.bat
rem 编写 bat 脚本并调用 powershell 脚本 
@echo off
setlocal
set "PGCLIENTENCODING=UTF8"
set "ROOT_DIR=%~dp0"
set "PSFILE=%ROOT_DIR%\modify_local_timestamp_to_utc_timestamp.ps1"
set "PSQL_BIN=%ROOT_DIR%\PostgreSQL\pgsql\bin\psql"
set "PGPORT=5432"
set "UPDATE_DB_SQL=%ROOT_DIR%\modify_local_timestamp_to_utc_timestamp.sql"
set "DB_NAME=postgres"
set "LOG_FILE=%ROOT_DIR%\modify_local_timestamp_to_utc_timestamp.log"

echo %date:~0,10%_%time%: [begin] powershell.exe -File "%PSFILE%"  -param1 "%PSQL_BIN%" -param2 "%PGPORT%" -param3 "%UPDATE_DB_SQL%" -param4 "%DB_NAME%" -param5 "%LOG_FILE%"  >> %LOG_FILE% 2>&1
powershell.exe -File "%PSFILE%"  -param1 "%PSQL_BIN%" -param2 "%PGPORT%" -param3 "%UPDATE_DB_SQL%" -param4 "%DB_NAME%" -param5 "%LOG_FILE%" 
echo %date:~0,10%_%time%: [end]   powershell.exe -File "%PSFILE%"  -param1 "%PSQL_BIN%" -param2 "%PGPORT%" -param3 "%UPDATE_DB_SQL%" -param4 "%DB_NAME%" -param5 "%LOG_FILE%"  >> %LOG_FILE% 2>&1

endlocal

测试效果

C:\>call modify_local_timestamp_to_utc_timestamp.bat

C:\>D:\PostgreSQL\pgsql\bin\psql -U postgres -d postgres
psql (15.6)
Type "help" for help.

postgres=# \d test
                                        Table "public.test"
  Column   |            Type             | Collation | Nullable |             Default
-----------+-----------------------------+-----------+----------+----------------------------------
 id        | integer                     |           | not null | nextval('test_id_seq'::regclass)
 time_info | timestamp without time zone |           |          |
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)


postgres=# select * from test;
 id |      time_info
----+---------------------
  1 | 2024-08-28 01:57:10
  2 | 2024-08-28 02:57:13
(2 rows)


postgres=# \q

Linux 环境

flag=$(date +%z | cut -b 1-1)
hour=$(date +%z | cut -b 2-3)
minute=$(date +%z | cut -b 4-5)
offset_time=$(echo ${flag}${hour}:${minute}:00)
/program/bin/psql -U postgres -d postgres -v DELTA_TIME="'${offset_time}'" -f modify_local_timestamp_to_utc_timestamp.sql >> modify_local_timestamp_to_utc_timestamp.log 2>&1