In the development process, we often store the milliseconds of datetime to the database, but its corresponding time seems very inconvenient, we can use some functions to convert milliseconds to date format.
First, in MySQL, there is a built-in function from_unixtime () to do the corresponding conversion, using the following:
Copy Code code as follows:
Mysql> Select From_unixtime (1, '%y-%m-%d%h:%i:%s ');
+---------------------------------------+
| From_unixtime (1, '%y-%m-%d%h:%i:%s ') |
+---------------------------------------+
| 1970-01-01 08:00:01 |
+---------------------------------------+
1 row in Set (0.00 sec)
The parameter units in the function from_unixtime are seconds, and since we are in +08:00 time zone, all the final results displayed are 1970-01-01 08:00:01.
Second, in Oracle, because there is no corresponding built-in function, we can only customize a function to complete the conversion.
Copy Code code as follows:
sql> Create or Replace function long2date (param in long) return VARCHAR2 as
V_text VARCHAR2 (64);
2 begin
3 Select To_char (to_date (' 1970-01-01 ', ' yyyy-mm-dd ') + (param/(24*60*60*1000) +
8/24-1/86400), ' Yyyy-mm-dd HH24:MI:SS ')
4 into V_text from dual;
5 return v_text;
6 End Long2date;
7/
function has been created.
Sql> Select Long2date (1000) from dual;
Long2date (1000)
--------------------------------------------------------------------------------------
1970-01-01 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 ')) * 86400000) long2date from dual;
Char_sysdate long2date
------------------- --------------------
2013-01-07 14:53:18 2013-01-07 14:53:17
1, the parameter units in the Long2date () function are milliseconds.
2, plus 8/24, because we are in the +08:00 time zone, so add 8 hours.
3, minus 1/86400, because it may be affected by the leap second, can be adjusted according to the actual needs. In this case, there is no need to subtract 1/86400.
4, using a similar method, we can convert the date to a long type of the number of milliseconds.
Copy Code code 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 ')) * 86400000 Current_milli from dual;
Current_date Current_milli
------------------- ----------------
2013-01-07 15:09:18 1357571358000
