前几日一客户问我在oracle执行unix_timestamp函数报错，心想这不是MySQL的函数吗？难道oracle引入了？找了一圈到oracle 23ai也没有自带该函数，可以自定义函数实现， 在去年的一个oracle迁移到PostgreSQL系数据库里，有套业务库大量使用epochs做为datetime，这里简单记录在oracle, mysql ,postgresql中如何做unix epoch到datetime的转换.
什么是UNIX EPOCHS
UNIX epoch是指从某日期到1970年1月1日UTC午夜(00:00)之后的秒数*。Oracle数据库没有将epoch转换为标准datetime值的内置函数，MySQL中有unix_timestamp和from_unixtime， PostgreSQL中有EXTRACT(EPOCH FROM xx)函数。
Tidb(MySQL)
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 579
Server version: 5.7.25-TiDB-v5.4.0 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select unix_timestamp(now()),now();
+-----------------------+---------------------+
| unix_timestamp(now()) | now() |
+-----------------------+---------------------+
| 1716000890 | 2024-05-18 10:54:50 |
+-----------------------+---------------------+
1 row in set (0.03 sec)
mysql> select from_unixtime( 1716000890);
+----------------------------+
| from_unixtime( 1716000890) |
+----------------------------+
| 2024-05-18 10:54:50 |
+----------------------------+
1 row in set (0.04 sec)
mysql> SELECT CONVERT_TZ('2024-05-18 10:54:50','+08:00','+00:00') as UTC;
+---------------------+
| UTC |
+---------------------+
| 2024-05-18 02:54:50 |
+---------------------+
1 row in set (0.04 sec)
mysql>
Oracle
# non-UTC time zones
SQL> select to_timestamp( '2024-05-18 11:01:18', 'yyyy-mm-dd hh24:mi:ss' ) DT ,
from_tz(to_timestamp( '2024-05-18 11:01:18', 'yyyy-mm-dd hh24:mi:ss' ),'Asia/Shanghai') at time zone 'UTC' TO_UTC
from dual;
2 3
DT TO_UTC
--------------------------------------------------------------------------- ---------------------------------------------------------------------------
18-MAY-24 11.01.18.000000000 AM 18-MAY-24 03.01.18.000000000 AM UTC
SQL>with v1 as
(
select
from_tz(to_timestamp( '2024-05-18 11:01:18', 'yyyy-mm-dd hh24:mi:ss' ),'Asia/Shanghai') at time zone 'UTC'
- timestamp'1970-01-01 00:00:00' epoch_dsi
from dual
)
select extract ( day from epoch_dsi ) * 86400
+ extract ( hour from epoch_dsi ) * 3600
+ extract ( minute from epoch_dsi ) * 60
/* remove fractional seconds */
+ trunc ( extract ( second from epoch_dsi ) ) ts_epoch
from v1;
TS_EPOCH
----------
1716030078
SQL> select /* Epoch to date */
date'1970-01-01'
+ (1716030078/ 86400 ) dt,
/* Epoch to timestamp */
timestamp'1970-01-01 00:00:00'
+ numtodsinterval ( 1716030078, 'second' ) ts
from dual;
SQL> select /* Epoch to date */
date'1970-01-01'
+ (1716030078/ 86400 ) dt,
/* Epoch to timestamp */
timestamp'1970-01-01 00:00:00'
+ numtodsinterval ( 1716030078, 'second' ) ts
from dual; 2 3 4 5 6 7
DT TS
------------------- ---------------------------------------------------------------------------
2024-05-18 11:01:18 18-MAY-24 11.01.18.000000000 AM
oracle 23ai 的JavaScript 存储过程
Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05
SQL> create or replace function epoch_to_timestamp (
"ep" number
) return timestamp
as mle language javascript
{{
var utcSeconds = ep;
var d = new Date(0);
d.setUTCSeconds(utcSeconds);
return d;
}};
/
Function created.
SQL> create or replace function timestamp_to_epoch (
"ts" timestamp
) return number
as mle language javascript
{{
var d = new Date(ts);
var utcSeconds = d.getTime() / 1000;
return utcSeconds;
}};
/
Function created.
SQL> select epoch_to_timestamp(1716030078) from dual;
EPOCH_TO_TIMESTAMP(1716030078)
---------------------------------------------------------------------------
18-MAY-24 11.01.18.000000000 AM
SQL> select timestamp_to_epoch(to_timestamp ( '2024-05-18 11:01:18', 'yyyy-mm-dd hh24:mi:ss' ) ) from dual;
TIMESTAMP_TO_EPOCH(TO_TIMESTAMP('2024-05-1811:01:18','YYYY-MM-DDHH24:MI:SS'))
-----------------------------------------------------------------------------
1716030078
MogDB(PostgreSQL)
MogDB=# SELECT EXTRACT(EPOCH FROM current_timestamp),current_timestamp;
date_part | pg_systimestamp
------------------+-------------------------------
1716001278.27793 | 2024-05-18 11:01:18.277927+08
(1 row)
MogDB=# select to_timestamp(1716001278.27793);
to_timestamp
------------------------------
2024-05-18 11:01:18.27793+08
MogDB=# SELECT
now() AS "Current Date/Time",
extract(epoch from now()) AS "Unix Timestamp",
to_timestamp(extract(epoch from now())) AS "And back again...";
Current Date/Time | Unix Timestamp | And back again...
-------------------------------+------------------+-------------------------------
2024-05-18 11:03:43.199629+08 | 1716001423.19963 | 2024-05-18 11:03:43.199629+08
(1 row)
— enjoy —