Solved

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

Posted on 2015-02-02
23
491 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
  • 9
  • 6
  • 4
  • +2
23 Comments
 
LVL 76

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 68

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 76

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
 
LVL 73

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 73

Expert Comment

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

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 73

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 76

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 34

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 73

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 73

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 73

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 73

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 73

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 73

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now