Hello,

I'm using Oracle 9i on Solaris. How do I take a column of a DATE type
and convert it to milliseconds that have elapsed since Jan. 1, 1970, 12
AM?

Thanks for any help, - Dave

laredotornado@zipmail.com wrote in news:1147093983.362115.82810
@y43g2000cwc.googlegroups.com:

> Hello,
>
> I'm using Oracle 9i on Solaris. How do I take a column of a DATE type
> and convert it to milliseconds that have elapsed since Jan. 1, 1970, 12
> AM?
>
> Thanks for any help, - Dave
>
>

You do NOT get millisecond from DATE; must use TIMESTAMP

ianal Vista wrote:
> laredotornado@zipmail.com wrote in news:1147093983.362115.82810
> @y43g2000cwc.googlegroups.com:
>
>> Hello,
>>
>> I'm using Oracle 9i on Solaris. How do I take a column of a DATE type
>> and convert it to milliseconds that have elapsed since Jan. 1, 1970, 12
>> AM?
>>
>> Thanks for any help, - Dave
>>
>>
>
> You do NOT get millisecond from DATE; must use TIMESTAMP

Hey, you could have been more helpful. Why didn't you suggest to
convert the DATE to TIMESTAMP and then extract ms?

SCNR

robert

Robert Klemme wrote:
> ianal Vista wrote:
>> laredotornado@zipmail.com wrote in news:1147093983.362115.82810
>> @y43g2000cwc.googlegroups.com:
>>
>>> Hello,
>>>
>>> I'm using Oracle 9i on Solaris. How do I take a column of a DATE type
>>> and convert it to milliseconds that have elapsed since Jan. 1, 1970, 12
>>> AM?
>>>
>>> Thanks for any help, - Dave
>>>
>>>
>>
>> You do NOT get millisecond from DATE; must use TIMESTAMP
>
> Hey, you could have been more helpful. Why didn't you suggest to
> convert the DATE to TIMESTAMP and then extract ms?
>
> SCNR
>
> robert

Converting a DATE to a TIMESTAMP will not yield any millisecond values
other than 0. You cannot obtain more precision out of a value in one
datatype by converting it to a different datatype. For instance, you
cannot convert an integer value 'X' to a floating point number and get
more than 'X.0' out of it.

HTH,
Brian


--
================================================== =================

Brian Peasland
oracle_dba@nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

Brian Peasland <oracle_dba@nospam.peasland.net> wrote:
> Robert Klemme wrote:
>> ianal Vista wrote:
>>> laredotornado@zipmail.com wrote in news:1147093983.362115.82810
>>> @y43g2000cwc.googlegroups.com:
>>>
>>>> Hello,
>>>>
>>>> I'm using Oracle 9i on Solaris. How do I take a column of a DATE type
>>>> and convert it to milliseconds that have elapsed since Jan. 1, 1970, 12
>>>> AM?
>>>>
>>>> Thanks for any help, - Dave
>>>>
>>>>
>>>
>>> You do NOT get millisecond from DATE; must use TIMESTAMP
>>
>> Hey, you could have been more helpful. Why didn't you suggest to
>> convert the DATE to TIMESTAMP and then extract ms?
>>
>> SCNR
>>
>> robert
>
> Converting a DATE to a TIMESTAMP will not yield any millisecond values
> other than 0. You cannot obtain more precision out of a value in one
> datatype by converting it to a different datatype. For instance, you
> cannot convert an integer value 'X' to a floating point number and get
> more than 'X.0' out of it.

Let me propose 'the punchline conjecture':

No matter how simple a joke is, there will always be someone who doesn't
get it, provided the audience is big enough.

Yours,
Laurenz Albe

> Let me propose 'the punchline conjecture':
>
> No matter how simple a joke is, there will always be someone who doesn't
> get it, provided the audience is big enough.

If it was a joke, then it should have included an emoticon of some
sort...i.e. or

Since there are no voice inflections or facial indicators to form a
basis of the tone, emoticons are all we have in this forum.

Cheers!
Brian




--
================================================== =================

Brian Peasland
oracle_dba@nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

Brian Peasland wrote:
>> Let me propose 'the punchline conjecture':
>>
>> No matter how simple a joke is, there will always be someone who doesn't
>> get it, provided the audience is big enough.
>
> If it was a joke, then it should have included an emoticon of some
> sort...i.e. or
>
> Since there are no voice inflections or facial indicators to form a
> basis of the tone, emoticons are all we have in this forum.

If you look closely at my post you'll discover the four letters
"SCNR"... I didn't want to make it too easy to spot. Also the use of
common sense is explicitly *not* prohibited in this forum - or was it
"is not explicitly prohibited"?



robert

But he probably does not need to be so precise, right ? He could create
a 'timestamp' column to replace the 'date' one, and accept to lose the
last second for the previous data, calculating the number through
something like this:

SQL> select time_diff('01-JAN-70',sysdate) * 1000 from dual;

TIME_DIFF('01-JAN-70',SYSDATE)
------------------------------
1147182144000

(code for time_diff : http://www.psoug.org/reference/date_func.html)