Other Oracle database topics - converting date to milliseconds?

Author

converting date to milliseconds?

laredotornado@zipmail.com

2005-03-31, 8:02 pm

Hello, I'm using Oracle 8.1.7 on Solaris. How would I write a select
statement to convert a table column of the DATE type to something
that's a NUMBER, preferrably with units of seconds or milliseconds?

Thanks, - Dave

DA Morgan

2005-03-31, 8:02 pm

laredotornado@zipmai
l.com wrote:
> Hello, I'm using Oracle 8.1.7 on Solaris. How would I write a select
> statement to convert a table column of the DATE type to something
> that's a NUMBER, preferrably with units of seconds or milliseconds?
>
> Thanks, - Dave

The best way would be to upgrade to a version of the Oracle database
that is supported in the current millenium.

Not only does the functionality exist ... it will greatly improve your
resume.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

Andy Hassall

2005-03-31, 8:02 pm

On 31 Mar 2005 13:11:06 -0800, laredotornado@zipmai
l.com wrote:

>Hello, I'm using Oracle 8.1.7 on Solaris. How would I write a select
>statement to convert a table column of the DATE type to something
>that's a NUMBER, preferrably with units of seconds or milliseconds?

Seconds since when? If you addition or subtraction between DATE datatypes it
converts it to a NUMBER indicating number of days, down to second resolution.

e.g. to get the standard Unix-style seconds-since-epoch timestamp:

SQL> select (sysdate- to_date('19700101','
YYYYMMDD'))*86400 from dual;

(SYSDATE- TO_DATE('19700101','
YYYYMMDD'))*86400
----------------------------------------------
1112308598

Millisecond accuracy only comes in at 9i with the TIMESTAMP datatype.

--
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

Sybrand Bakker

2005-03-31, 8:02 pm

On Thu, 31 Mar 2005 13:34:07 -0800, DA Morgan
<damorgan@x.washington.edu> wrote:

>The best way would be to upgrade to a version of the Oracle database
>that is supported in the current millenium.
>
>Not only does the functionality exist ... it will greatly improve your
>resume.

8.1.7 definitely was supported in the current millenium. And
apparently you don't have any customers who don't want to upgrade.
Your mantra's are becoming a bit boring, and in this case your mantra
isn't even true: 8i was desupported on December 31, 2004.

--
Sybrand Bakker, Senior Oracle DBA

Malcolm Dew-Jones

2005-03-31, 8:02 pm

Sybrand Bakker (postbus@sybrandb.demon.nl) wrote:
: On Thu, 31 Mar 2005 13:34:07 -0800, DA Morgan
: <damorgan@x.washington.edu> wrote:

: >The best way would be to upgrade to a version of the Oracle database
: >that is supported in the current millenium.
: >
: >Not only does the functionality exist ... it will greatly improve your
: >resume.

: 8.1.7 definitely was supported in the current millenium. And
: apparently you don't have any customers who don't want to upgrade.

Indeed, upgrading important production systems is not a trivial task.

I know of a dba who just finished a _two_year_ project upgrading various
production databases from 8.something to 9.something. (Though they weren't
working full time on that one task.)

google on "royal bank upgrade fiasco" or goto
http://www.nupge.ca/news_2004/n08jn04b.htm

for an example of why of upgrades need to be done with extreme care.

--

This space not for rent.

Mike Cretan

2005-03-31, 8:02 pm

Hi Dave,

It very much depends what you want to use it for if its to be of any use,
but if you just want a numerical representation of the date, then you could
do something like:

select to_number(to_char(sy
sdate,'YYYYMMDDHH24M
ISS'))
from dual

Which will return the current date as:

20050401083413

If its millisecond timestamps you're interested in, you might want to check
out the DBMS_UTILITY.GET_TIME supplied PL/SQL package -
http://download-west.oracle.com/doc...ut2.htm#1002103

Cheers,

Mike Cretan

< laredotornado@zipmai
l.com> wrote in message
news:1112303466.399850.177620@g14g2000cwa.googlegroups.com...
> Hello, I'm using Oracle 8.1.7 on Solaris. How would I write a select
> statement to convert a table column of the DATE type to something
> that's a NUMBER, preferrably with units of seconds or milliseconds?
>
> Thanks, - Dave
>

DA Morgan

2005-03-31, 8:02 pm

Sybrand Bakker wrote:
> On Thu, 31 Mar 2005 13:34:07 -0800, DA Morgan
> <damorgan@x.washington.edu> wrote:
>
>
>
>
> 8.1.7 definitely was supported in the current millenium. And
> apparently you don't have any customers who don't want to upgrade.
> Your mantra's are becoming a bit boring, and in this case your mantra
> isn't even true: 8i was desupported on December 31, 2004.
>
>
> --
> Sybrand Bakker, Senior Oracle DBA

Darn ... can't even use decade. Oh well. Time to come up with something
else to say. Still the point is that 8i has begun desupport and it is
time for people to move to 10g to obtain the functionality they need.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

Sponsored Links