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

Source: Internet
Author: User
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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.