Solved

Oracle SQL Query: Convert Number to Date using a query.

Posted on 2015-02-02
23
680 Views
Last Modified: 2015-02-26
I have written the query below which returns dates.

select 
  OBJECTID, 
  CREATEDDATE,
  MODIFIEDDATE,
  LAST_ACCESSEDDATE,
  EMAIL_SUBMITTIME
 from OBJECTS

Open in new window

Attached I have a screenshot of the results.  I wanted to see if anyone had a suggestion on converting the number to a date.  It is apparent by the name of the columns that these are intended to be dates – but I may be wrong.

I am fluent in Microsoft SQL, so the way these dates are stored is foreign to me.  Any help would be greatly appreciated.


results of query
0
Comment
Question by:rye004
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
  • 4
  • +2
23 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40584769
>>Convert Number to Date using a query

to_date(number_column,'XXX')

where XXX is the format mask that matches the data:
YYYYMMDD
DDMMYYYY
etc...

>>Attached I have a screenshot of the results.

Don't see them.
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 40584779
Looks like "seconds after 1970-01-01" or similar. You'll need at least one well-known reference date to be certain about the exact base.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40584782
>>Don't see them.

Looks like you edited the question as I was posting.

Any idea what format that is in?

I first thought it was epoch time but I don't think so.

If it was Epoch time:
select to_date('01/01/1970', 'MM/DD/YYYY') + (epoch_time_value/ 86400000) from some_table;
0
Congratulations! You’re Certified – Now What?

Starting a new career can be overwhelming. Becoming certified in your field of expertise is a great start, but where do you go from here?  Here are some tips to help you on your career journey.

 
LVL 74

Expert Comment

by:sdstuber
ID: 40584803
Do you know what at least one of the values translates to when it becomes a date?


Unless they are nanoseconds since epoch the result is a few millennia away.
If they are nanoseconds, then the dates are 40 years old
0
 

Author Comment

by:rye004
ID: 40584822
Below I have an example of the date format:

12/30/2014 12:01:43 PM (2014-12-30 20:01:43 UTC)

I hope this helps.

Many Thanks
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40584828
thanks, but which numeric value does that data correspond to?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40584830
>>I have an example of the date format: 12/30/2014 12:01:43 PM (2014-12-30 20:01:43 UTC)

OK, do you know what the numbers stored represent?  They don't look like standard Unix Epoch.
0
 

Author Comment

by:rye004
ID: 40584868
Sorry, I don’t know.  My client has asked for me to pull information from this database.  The original developer is no longer around and did not keep notes, so I am unclear regarding some items.

What I can provide is the value in the database and what it look in the GUI.  For example, in the database I see “130644433030671670” but the user interface shows “12/30/2014 12:01:43 PM (2014-12-30 20:01:43 UTC)”.

Is that helpful?  I can get more examples if needed.

Many Thanks
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40584883
>>> "130644433030671670” but the user interface shows “12/30/2014 12:01:43 PM (2014-12-30 20:01:43 UTC)”.

that's exactly what we needed

a few more examples might help identify the pattern
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40584891
Getting ready to leave for the day.

That looks like an LDAP timestamp:
http://www.epochconverter.com/epoch/ldap-timestamp.php

The timestamp is the number of 100-nanoseconds intervals (1 nanosecond = one billionth of a second) since Jan 1, 1601 UTC.

If no one else gets to this before I get home, I'll play later.
0
 
LVL 35

Expert Comment

by:johnsone
ID: 40584923
Thanks to slightwv for the research.

It looks like his post is correct.  If you use this query:
SELECT objectid, 
       To_date('01011601', 'mmddyyyy') + ( createddate / 864000000000 ) 
       CREATEDDATE, 
       To_date('01011601', 'mmddyyyy') + ( modifieddate / 864000000000 ) 
       MODIFIEDDATE, 
       To_date('01011601', 'mmddyyyy') + ( last_accesseddate / 864000000000 ) 
       LAST_ACCESSEDDATE, 
       To_date('01011601', 'mmddyyyy') + ( email_submittime / 864000000000 ) 
       EMAIL_SUBMITTIME 
FROM   objects; 

Open in new window

It should convert the times to an Oracle DATE datatype value.  It will be at the UTC timezone.  If you need to change the time zone, look at the NEW_TIME function.  That will convert the time zone to a new one.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40584932
yep

select date '1601-01-01' + 130644433030671670/864000000000 from dual;

2014-12-30 20:01:43
0
 

Author Comment

by:rye004
ID: 40584958
Below are some more samples.  Please let me know if you need anything else.  Many thanks!


130642432816461160
12/28/2014 4:28:01 AM (2014-12-28 12:28:01 UTC)


130607217640045990
11/17/2014 10:16:04 AM (2014-11-17 18:16:04 UTC)


130560500153252300
9/24/2014 9:33:35 AM (2014-09-24 16:33:35 UTC)
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40585008
the rounding is a little bit odd

I get 2014-12-28 12:28:02  for 130642432816461160

but the other 3 matchup
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40585013
got it, round to the 5th decimal and they all match

WITH sampledata
     AS (SELECT 130644433030671670 n, TO_DATE('2014-12-30 20:01:43', 'yyyy-mm-dd hh24:mi:ss') gmt
           FROM DUAL
         UNION ALL
         SELECT 130642432816461160 n, TO_DATE('2014-12-28 12:28:01', 'yyyy-mm-dd hh24:mi:ss') gmt
           FROM DUAL
         UNION ALL
         SELECT 130607217640045990 n, TO_DATE('2014-11-17 18:16:04', 'yyyy-mm-dd hh24:mi:ss') gmt
           FROM DUAL
         UNION ALL
         SELECT 130560500153252300 n, TO_DATE('2014-09-24 16:33:35', 'yyyy-mm-dd hh24:mi:ss') gmt
           FROM DUAL)
SELECT n, gmt, DATE '1601-01-01' + ROUND(n / 864000000000, 5)
  FROM sampledata;
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40585022
and with your formatting


WITH sampledata
     AS (SELECT 130644433030671670 n, TO_DATE('2014-12-30 20:01:43', 'yyyy-mm-dd hh24:mi:ss') gmt
           FROM DUAL
         UNION ALL
         SELECT 130642432816461160 n, TO_DATE('2014-12-28 12:28:01', 'yyyy-mm-dd hh24:mi:ss') gmt
           FROM DUAL
         UNION ALL
         SELECT 130607217640045990 n, TO_DATE('2014-11-17 18:16:04', 'yyyy-mm-dd hh24:mi:ss') gmt
           FROM DUAL
         UNION ALL
         SELECT 130560500153252300 n, TO_DATE('2014-09-24 16:33:35', 'yyyy-mm-dd hh24:mi:ss') gmt
           FROM DUAL)
SELECT n,
       gmt,
       DATE '1601-01-01' + ROUND(n / 864000000000, 5),
       TO_CHAR(
           FROM_TZ(CAST(DATE '1601-01-01' + ROUND(n / 864000000000, 5) AS TIMESTAMP), 'GMT')
               AT TIME ZONE 'US/Pacific',
           'mm/dd/yyyy hh:mi:ss AM'
       ) ||
        TO_CHAR(
          DATE '1601-01-01' + ROUND(n / 864000000000, 5),
           ' (yyyy-mm-dd hh24:mi:ss "UTC)"'
       )
  FROM sampledata;

and using your table and columns, try this...


SELECT objectid,
          TO_CHAR(
              FROM_TZ(
                  CAST(DATE '1601-01-01' + ROUND(createddate / 864000000000, 5) AS TIMESTAMP),
                  'GMT'
              )
                  AT TIME ZONE 'US/Pacific',
              'mm/dd/yyyy hh:mi:ss AM'
          )
       || TO_CHAR(
              DATE '1601-01-01' + ROUND(createddate / 864000000000, 5),
              ' (yyyy-mm-dd hh24:mi:ss "UTC)"'
          ),
          TO_CHAR(
              FROM_TZ(
                  CAST(DATE '1601-01-01' + ROUND(modifieddate / 864000000000, 5) AS TIMESTAMP),
                  'GMT'
              )
                  AT TIME ZONE 'US/Pacific',
              'mm/dd/yyyy hh:mi:ss AM'
          )
       || TO_CHAR(
              DATE '1601-01-01' + ROUND(modifieddate / 864000000000, 5),
              ' (yyyy-mm-dd hh24:mi:ss "UTC)"'
          ),
          TO_CHAR(
              FROM_TZ(
                  CAST(DATE '1601-01-01' + ROUND(last_accesseddate / 864000000000, 5) AS TIMESTAMP),
                  'GMT'
              )
                  AT TIME ZONE 'US/Pacific',
              'mm/dd/yyyy hh:mi:ss AM'
          )
       || TO_CHAR(
              DATE '1601-01-01' + ROUND(last_accesseddate / 864000000000, 5),
              ' (yyyy-mm-dd hh24:mi:ss "UTC)"'
          ),
          TO_CHAR(
              FROM_TZ(
                  CAST(DATE '1601-01-01' + ROUND(email_submittime / 864000000000, 5) AS TIMESTAMP),
                  'GMT'
              )
                  AT TIME ZONE 'US/Pacific',
              'mm/dd/yyyy hh:mi:ss AM'
          )
       || TO_CHAR(
              DATE '1601-01-01' + ROUND(email_submittime / 864000000000, 5),
              ' (yyyy-mm-dd hh24:mi:ss "UTC)"'
          )
  FROM objects;
0
 

Author Comment

by:rye004
ID: 40585028
I typed that one incorrectly.  Sorry.
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 40585039
>>> I typed that one incorrectly.  Sorry.

ok, in that case,  don't do the ROUND(....,5)

just take the division as is and apply it


SELECT objectid,
          TO_CHAR(
              FROM_TZ(CAST(DATE '1601-01-01' + (createddate / 864000000000) AS TIMESTAMP), 'GMT')
                  AT TIME ZONE 'US/Pacific',
              'mm/dd/yyyy hh:mi:ss AM'
          )
       || TO_CHAR(
              DATE '1601-01-01' + (createddate / 864000000000),
              ' (yyyy-mm-dd hh24:mi:ss "UTC)"'
          ),
          TO_CHAR(
              FROM_TZ(CAST(DATE '1601-01-01' + (modifieddate / 864000000000) AS TIMESTAMP), 'GMT')
                  AT TIME ZONE 'US/Pacific',
              'mm/dd/yyyy hh:mi:ss AM'
          )
       || TO_CHAR(
              DATE '1601-01-01' + (modifieddate / 864000000000),
              ' (yyyy-mm-dd hh24:mi:ss "UTC)"'
          ),
          TO_CHAR(
              FROM_TZ(
                  CAST(DATE '1601-01-01' + (last_accesseddate / 864000000000) AS TIMESTAMP),
                  'GMT'
              )
                  AT TIME ZONE 'US/Pacific',
              'mm/dd/yyyy hh:mi:ss AM'
          )
       || TO_CHAR(
              DATE '1601-01-01' + (last_accesseddate / 864000000000),
              ' (yyyy-mm-dd hh24:mi:ss "UTC)"'
          ),
          TO_CHAR(
              FROM_TZ(
                  CAST(DATE '1601-01-01' + (email_submittime / 864000000000) AS TIMESTAMP),
                  'GMT'
              )
                  AT TIME ZONE 'US/Pacific',
              'mm/dd/yyyy hh:mi:ss AM'
          )
       || TO_CHAR(
              DATE '1601-01-01' + (email_submittime / 864000000000),
              ' (yyyy-mm-dd hh24:mi:ss "UTC)"'
          )
  FROM objects;
0
 

Author Comment

by:rye004
ID: 40591767
I wanted to say thank you to everyone for your help.
0
 

Author Comment

by:rye004
ID: 40591772
Thanks!
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40591830
slightwv should get the biggest chunk of the split.

he's the one that figured out the critical piece - i.e. the base date.

my addition was the formatting and correlating the US/Pacific time conversion


note, using NEWTIME as suggested above isn't reliable, because it won't automatically handle daylight saving time switches.  Use the timestamp conversions for proper handling.

the last example data posted illustrates the need for dst handling
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question