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

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
rye004Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
sdstuberConnect With a Mentor Commented:
>>> 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
 
slightwv (䄆 Netminder) Commented:
>>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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
slightwv (䄆 Netminder) Commented:
>>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
 
sdstuberCommented:
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
 
rye004Author Commented:
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
 
sdstuberCommented:
thanks, but which numeric value does that data correspond to?
0
 
slightwv (䄆 Netminder) Commented:
>>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
 
rye004Author Commented:
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
 
sdstuberCommented:
>>> "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
 
slightwv (䄆 Netminder) Commented:
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
 
johnsoneSenior Oracle DBACommented:
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
 
sdstuberCommented:
yep

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

2014-12-30 20:01:43
0
 
rye004Author Commented:
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
 
sdstuberCommented:
the rounding is a little bit odd

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

but the other 3 matchup
0
 
sdstuberCommented:
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
 
sdstuberCommented:
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
 
rye004Author Commented:
I typed that one incorrectly.  Sorry.
0
 
rye004Author Commented:
I wanted to say thank you to everyone for your help.
0
 
rye004Author Commented:
Thanks!
0
 
sdstuberCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.