以SQL转换LDAP日期(毫秒为纳秒) [英] Convert LDAP Date in SQL (milliseconds to nanoseconds)

问题描述

我正在获取milliseconds格式的日期.

I am getting date in milliseconds format.

即:1427342400000 is essentially 3/26/2015.

现在，我想进一步转换nanoseconds中的日期，并在Active Directory的accountexpires属性中传递此值.

Now I want to further convert date in nanoseconds and pass this value in accountexpires attribute in Active Directory.

将毫秒日期转换为纳秒日期的查询是什么?

What is the query to convert milliseconds date to nanoseconds date?

任何帮助将不胜感激.

推荐答案

现在我想进一步转换日期(以纳秒为单位)

Now I want to further convert date in nanoseconds

好的，但是从什么时候开始转换为纳秒?看您的示例，我假设起点是'01-JAN-1970'.

Ok, but convert to nano seconds since when? Looking at your example, I assume the start point to be '01-JAN-1970'.

如果我要自01-JAN-1970起将日期转换为毫秒，我会这样做:

If I want to convert a date to milliseconds since 01-JAN-1970, I would do this:

SQL> SELECT to_number(SYSDATE - to_date('01-JAN-1970','DD-MON-YYYY')) * (24 * 60 * 60 * 1000) milliseconds
  2  FROM dual;

        MILLISECONDS
--------------------
       1424428022000

SQL>

现在

1毫秒= 1000000纳秒

1 millisecond = 1000000 nanosecond

，使用此公式，只需乘以日期差即可.

, using this formula, just multiply it to the difference of the date.

将日期转换为纳秒:

SQL> SELECT to_number(SYSDATE - to_date('01-JAN-1970','DD-MON-YYYY')) * (24 * 60 * 60 * 1000 * 1000000) nanoseconds
  2  FROM dual;

         NANOSECONDS
--------------------
 1424428111000000000

SQL>

要将其转换回日期:

SQL> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL> SELECT TO_CHAR(to_date('1970-01-01 00','yyyy-mm-dd hh24')
  2         +
  3        (1424428111000000000 )/1000000/1000/60/60/24 , 'YYYY-MM-DD HH12:MI:SS am') nano_to_date
  4  FROM dual;

NANO_TO_DATE
----------------------
2015-02-20 10:28:31 am

SQL>

更新关于TIMEZONE

最好明确提及时区.

您可以在文字本身中提及时区，或将其强制转换为UTC并将其转换为您的local timezone.

You could mention the timezone in the literal itself, or cast it as UTC and convert it to your local timezone.

请参阅此答案.

这篇关于以SQL转换LDAP日期(毫秒为纳秒)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！