How to convert millisecond to date in the database

During the development process, we often store the time in milliseconds to the database, but the corresponding time seems very inconvenient. We can use some functions to convert the time to the date format.

1. in MySQL, there is a built-in function from_unixtime () for conversion, using the following:
Copy codeThe Code is as follows:
Mysql> select from_unixtime (1, '% Y-% m-% d % H: % I: % s ');
+ --------------------------------------- +
| From_unixtime (1, '% Y-% m-% d % H: % I: % s') |
+ --------------------------------------- +
| 08:00:01 |
+ --------------------------------------- +
1 row in set (0.00 sec)

The unit of the parameter in the from_unixtime function is seconds. Since we are in the + 08:00:01 time zone, all the final results are.

2. in Oracle, since there is no built-in function, we can only customize one function to complete the conversion.
Copy codeThe Code is as follows:
SQL> create or replace function long2date (param in long) return varchar2
V_text varchar2 (64 );
2 begin
3 select to_char (to_date ('1970-01-01 ', 'yyyy-MM-DD') + (param/(24*60*60*1970) +
8/24-1/86400), 'yyyy-MM-DD HH24: MI: ss ')
4 into v_text from dual;
5 return v_text;
6 end long2date;
7/

The function has been created.

SQL> select long2date (1000) from dual;

LONG2DATE (1000)
Bytes --------------------------------------------------------------------------------------
08:00:00

SQL> select to_char (sysdate, 'yyyy-MM-DD HH24: MI: ss') char_sysdate, long2date (
(Sysdate-8/24-to_date ('1970-01-01 ', 'yyyy-MM-DD') * 1970) long2date from dual;

CHAR_SYSDATE LONG2DATE
---------------------------------------
2013-01-07 14:53:18 2013-01-07 14:53:17

1. The parameter unit in the long2date () function is millisecond.

2. Add 8/24 because we are in the + time zone, so we need to add 8 hours.

3. Subtract 1/86400 because it may be affected by the leap second. You can adjust it as needed. In this example, there is no need to subtract 1/86400.

4. Using a similar method, we can convert a date to a long value in milliseconds.
Copy codeThe Code is as follows:
SQL> col current_milli format 999999999999999;
SQL> select to_char (sysdate, 'yyyy-MM-DD HH24: MI: ss') current_date, (sysdate-
To_date ('1970-01-01 ', 'yyyy-MM-DD') * 1970 current_milli from dual;

CURRENT_DATE CURRENT_MILLI
-----------------------------------
2013-01-07 15:09:18 1357571358000