Re: Customised DATE Trunc command

From: Myron Wintonyk <mwintony_at_med.ualberta.ca>
Date: Thu, 12 Oct 2000 14:49:34 +1000
Message-ID: <39E542DE.2F48BB2A@med.ualberta.ca>

What version or Oracle are you using? If it is Oracle 8i, you can create function
based indexes. Then, you could use queries to access these funciton baseds indexes.

Foe example:

  create index DAYS on tablename ( to_date( to_char( DATECOLUMN-0.25, 'YYYYMMDD'), 'YYYYMMDD' ) )
  create index WEEKS on tablename ( to_date( to_char( mod(DATECOLUMN-0.25),7), 'YYYYMMDD'), 'YYYYMMDD' )   create index MONTHS on tablename ( to_char( DATECOLUMN-0.25, 'YYYYMM') )   create index YEARS on tablename ( to_char( DATECOLUMN-0.25, 'YYYY' ) )

Further, you can create view to access the tables:

create or replace view DateTimeView as
select tablename.*,

        to_date( to_char( DATECOLUMN-0.25, 'YYYYMMDD'), 'YYYYMMDD' ) as DAYS,
        to_date( to_char( mod(DATECOLUMN-0.25),7), 'YYYYMMDD'), 'YYYYMMDD' ) as WEEKS,

        to_char( DATECOLUMN-0.25, 'YYYYMM') ) as MONTHS,
        to_char( DATECOLUMN-0.25, 'YYYY' ) ) as YEARS

    from tablename

If the table is not too big, then the indexes will not be necessary either. Please note that I have not tested the above code out, so there may be a typo in it. But Hopefully, you get the idea. The key is the subtraction of 0.25 from the datefield to makethe 6:00 thing happen.

Paul J wrote:

> Re all,
>
> I need to use the TRUNC(date, '...') command to group data into separate
> groups.
>
> The problem I have is that the default options for the TRUNC command are
> not the ones that I want. Instead, I need to start a day at 6am rather
> than at midnight (which is the Oracle default for the TRUNC command),
> and ends at 5:59am on the next day.
>
> Also I need to change this to handle, WEEKS (start on Monday rather than
> Sunday (Oracle default)), MONTHS, YEARS. All these options need to
> start at 6am on the first day within that time period.
>
> I was wondering if anyone has an efficient way of producing a solution
> to this problem.
>
> My first thought was have a function that takes the actual date and the
> period I want, then returns the date truncated to the appropriate date.
> This function would have to be used within the SELECT clause of an SQL
> statement, so I would do a pragma restrict_references on it. And it
> would also be used within the GROUP BY clause of the same select
> statement.
>
> When I think of this, I think: My God, that is going to be one hell of a
> slow SQL statement.
>
> Does anyone have any better suggestions or solutions to my problem.
>
> Thanks.
> Paul J
Received on Wed Oct 11 2000 - 23:49:34 CDT