【YashanDB 知识库】MySQL 的 FROM_UNIXTIME 和 UNIX_TIMESTAMP 如何在 YashanDB 改写

sql 代码解读复制代码mysql> select FROM_UNIXTIME( 8640000 + UNIX_TIMESTAMP('2024-01-01 00:00:00')) from dual;

+-------------------------------------------------------------------+

| FROM_UNIXTIME( 8640000 + UNIX_TIMESTAMP('2024-01-01 00:00:00')) |

+-------------------------------------------------------------------+

| 2024-04-10 00:00:00 |

+-------------------------------------------------------------------+

1 row in set (0.00 sec)

csharp 代码解读复制代码SQL> select FROM_UNIXTIME( 8640000 + UNIX_TIMESTAMP('2024-01-01 00:00:00')) from dual;


[1:8]YAS-04243 invalid identifier "FROM_UNIXTIME"

sql 代码解读复制代码CREATE OR REPLACE FUNCTION SYS.FROM_UNIXTIME

(

  TIMESTAMP_ IN NUMBER

) RETURN DATE AS

SECONDS_PER_DAY NUMBER := 86400;

MASK VARCHAR2(50 CHAR) := 'YYYY-MM-DD HH24:MI:SS';

REF_DATE DATE;

BEGIN

  REF_DATE := TO_DATE('1970-01-01 00:00:00', MASK);

  RETURN REF_DATE + (TIMESTAMP_/SECONDS_PER_DAY);

END FROM_UNIXTIME;

/

 

 

CREATE OR REPLACE FUNCTION SYS.UNIX_TIMESTAMP

(

  DATE_ IN DATE

) RETURN NUMBER AS

SECONDS_PER_DAY NUMBER := 86400;

DIFF_BETWEEN_DATES NUMBER;

MASK VARCHAR2(50 CHAR) := 'YYYY-MM-DD HH24:MI:SS';

REF_DATE DATE;

BEGIN

  REF_DATE := TO_DATE('1970-01-01 00:00:00', MASK);

  DIFF_BETWEEN_DATES := DATE_ - REF_DATE;

  RETURN DIFF_BETWEEN_DATES * SECONDS_PER_DAY;

END UNIX_TIMESTAMP;

/

 

CREATE PUBLIC SYNONYM FROM_UNIXTIME FOR SYS.FROM_UNIXTIME;

CREATE PUBLIC SYNONYM UNIX_TIMESTAMP FOR SYS.UNIX_TIMESTAMP;

sql 代码解读复制代码SQL> select FROM_UNIXTIME( 8640000 + UNIX_TIMESTAMP('2024-01-01 00:00:00')) from dual;

FROM_UNIXTIME(864000

------------------------------------------------

2024-04-10 00:00:00

1 row fetched.