How to convert millisecond to date format in the database _ MySQL

In the development process, we often save the number of milliseconds in the date and time to the database, but the corresponding time seems very inconvenient, some functions can be used to convert data in milliseconds to the date format.

1. in MySQL, there is a built-in function from_unixtime () for conversion, using the following:

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.

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.

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