ANBOB™

前几日一客户问我在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 —

打赏

微信扫一扫，打赏作者吧～