5 Replies Latest reply: Dec 7, 2012 8:03 AM by APC RSS

    Number datatype to timestamp

    saranya.a
      Hi,

      i have date column in the db as datatype Number - 1354874161000 . i want a query to convert this into timestamp. pls help
        • 1. Re: Number datatype to timestamp
          APC
          What sort of timestamp is it?

          It's not an Oracle timestamp rendered as a number because the figures don't match to meaning time elements. Unless the joker who stored the data forgot to pad the parts with leading zeroes, it which case you have absolutely no chance of converting the string into a timestamp with any degree of certainty about the validity of the outcome.

          Cheers, APC
          • 2. Re: Number datatype to timestamp
            Rahul_India
            APC wrote:
            What sort of timestamp is it?

            It's not an Oracle timestamp rendered as a number because the figures don't match to meaning time elements. Unless the joker who stored the data forgot to pad the parts with leading zeroes, it which case you have absolutely no chance of converting the string into a timestamp with any degree of certainty about the validity of the outcome.

            Cheers, APC
            :D
            • 3. Re: Number datatype to timestamp
              chris227
              this?
              select
              to_char(
              to_date('01011970','DDMMYYYY')
              +
              numtodsinterval(1354874161000/1000, 'SECOND')
              ,'YYYY-MM-DD HH24:MI:SS') d
              from dual
              
              D 
              2012-12-07 09:56:01 
              Do you really need timestamp? Do you have values with franctional seconds?

              Edited by: chris227 on 07.12.2012 05:04
              1354874161000 instead of 354874161000

              Edited by: chris227 on 07.12.2012 05:05
              • 4. Re: Number datatype to timestamp
                Stew Ashton
                In Oracle, a date includes the time component: hours, minutes and seconds.

                The timestamp gives you fractional seconds.

                If you really need a timestamp, then:
                select to_timestamp(date '1970-01-01') +
                numtodsinterval(1354874161000/1000, 'second') from dual;
                You only need the TO_CHAR when you are formatting for display.

                Congrats to Chris for figuring out what you wanted!
                • 5. Re: Number datatype to timestamp
                  APC
                  Stew Ashton wrote:
                  Congrats to Chris for figuring out what you wanted!
                  Yes. I discounted a unix timestamp because it was too long. I didn't consider thousandths of a second.

                  Cheers, APC