Help with YeartoDate and Last12months 1

[MrHelpMe]

Hello experts,

Need help figuring out YeartoDate and Last12Months syntax. I'm trying experts, trying my best.

YearToDate = is it

select * from table where convert_to_epoch(arrival_time) between TRUNC(SYSDATE,'YEAR') and sysdate;


Last12Months

select * from table where convert_to_epoch(arrival_time) between last_day(add_months(sysdate, -12)) + 1 and sysdate from dual;

Now if this is correct why do I get the error Date Format Picture Ends before converting entire Input String. My epoch function converts field to format 30-OCT-2000 10:05:47

 

[SantaMufasa]

select * from table where convert_to_epoch(arrival_time) between last_day(add_months(sysdate, -12)) + 1 and sysdate from dual;

Is this really the syntax that you were trying to make work?...You are doing a "SELECT * from table" followed close behind with "...and sysdate from dual;"...that just won't work in Oracle.

If that's just a "mis-copy" into this thread, then we need to see the following posted here to help you troubleshoot:

1) "describe table" (the one from which you are reading for this example)
2) the code for your "convert_to_epoch" function.

Please post these two items to start with, then we can proceed.


Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[MrHelpMe]

Sorry SantaMufasa,

Actually it was a mistype. The table is hpd_helpdesk
So it would be
For Last 12 Mths

select * from hpd_helpdesk where convert_to_epoch(arrival_time) between last_day(add_months(sysdate, -12)) + 1 and sysdate;

and
For Year to date
select * hpd_helpdesk table where convert_to_epoch(arrival_time) between TRUNC(SYSDATE,'YEAR') and sysdate;

The function is as follows.
create java source named "EpochDateConverter" as
import java.lang.String;
import java.lang.System;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

public class EpochDateConverter {
public static String convert(final int secondsFromEpoch) {

final Calendar cal = Calendar.getInstance();

// Initialize a Calendar to the beginning of the epoch (1/1/1970 12:00:00 AM GMT), by using a
// java.util.Date starting at "0" milliseconds from the beginning of the epoch.
//
// Note: you cannot simply initialize the date with "secondsFromEpoch * 1000" because that
// results in a number to large and wrapping occurs.
cal.setTime(new Date(0));
cal.add(Calendar.SECOND, secondsFromEpoch);

// Format the date as a string with enough detail to build a precise Oracle DATE type
final DateFormat formatter = new SimpleDateFormat("dd-MMM-yyyy HH:mm:ss");
return formatter.format(cal.getTime()).toUpperCase();
}
}
;
/

create or replace function to_date_from_epoch
(p_seconds_from_epoch IN number)
return varchar2 as language java
name 'EpochDateConverter.convert(int) return java.lang.String'
;
/

Hope the function will help someone else.

 

[SantaMufasa]

MrHelpMe,

Still seeing some ambiguities here: The function (to_date_from_epoch) which appears in your most recent post takes an total number of seconds since midnight on the morning of January 1, 1970, and converts them into a string that is Oracle-DATE-ready (i.e., UTC -> Oracle Date) (but still returning a STRING/VARCHAR and not yet an Oracle DATE).

Yet, in your code earlier in the post, instead of referring to "to_date_from_epoch", you say

select * from hpd_helpdesk
where [B][I]convert_to_epoch[/I][/B](arrival_time)
      between last_day(add_months(sysdate, -12)) + 1
          and sysdate;

Unless you have a goofy naming convention, it seems to me that you are going the wrong direction (i.e., the function name implies that you ask to convert_to_epoch, which probably gives you seconds since midnight on the morning of January 1, 1970, instead of the Oracle DATE that your code rightfully expects. This "logic error" certainly would account for the "run-time error message" that you are receiving.

Additionally, you have a ton of code to do what Oracle can accomplish natively with a "one-liner" of code:

create or replace function utc_to_oracle (utc_in in number) return date is
begin
	return to_date('01.01.70','dd.mm.rr')+(utc_in/(60*60*24));
end;
/

Function created.

(Proof of concept)
SQL> select to_char(utc_to_oracle(1145709751),'dd-MON-yyyy hh24:mi:ss')
       from dual;

TO_CHAR(UTC_TO_ORACL
--------------------
22-APR-2006 12:42:31

Conversely, you can go from DATE to UTC with:

create or replace function utc_from_oracle (date_in in date) return number is
begin
	return (date_in-to_date('01.01.70','dd.mm.rr'))*(60*60*24);
end;
/

Function created.

(Proof of concept)
SQL> select utc_from_oracle(sysdate) from dual;

UTC_FROM_ORACLE(SYSDATE)
------------------------
              1145709751

Let us know if a) my presumptions were correct (about "data direction") and b) if the tighter Oracle code is useful.


Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[MrHelpMe]

Hello again,

Yes my grammatical errors are all over this forum In my original post I was just trying to give you an idea about what I was trying to do and was improperly naming functions and tables. Your are correct in saying that my function name is total different in my most recent post. So let's try this again. The function is called to_date_from epoch and what is does is a follows. This is not my function but have been given the permission to use it.

Epoch Time Conversion
Some applications (like Remedy ARS, for example) store date and time information as the number of seconds since the beginning of the epoch. The beginning of the epoch, in most cases, is defined as January 1, 1970 12:00:00 AM GMT. When converting date and time data stored this way to an Oracle date format, simply adding the given number of seconds to the beginning of the epoch is not sufficient, since this results in the GMT date/time, not the local date/time. The timezone offset must also be applied. This can be complex in areas where DST (daylight savings time) applies, because the offset changes by an hour twice per year.
The following Java stored procedure and accompanying function provide a simple mechanism for converting epoch time to an Oracle date in the local time. I used Java (as opposed to PL/SQL) because the java.util.Date and java.util.Calendar classes automatically take the local timezone, and whether DST in in effect, into consideration so we don't have to.

Hope this clarifies. I just want to take the value of that function and compare it with my formulas for YearToDate and Last12 Months but I am not sure if my formulas are correct. Sorry for any ambiguity.

 

[SantaMufasa]

Aaah yes, I should have been using my crystal ball to disclose all of the additional hidden parameters of your need <grin>.

In that case, you can use the following variations to my earlier code to accommodate timezone issues:

create or replace function CONVERT_UTC_DATE
	(utc_in in number, timezone_abbrev varchar2) return date is
begin
    return new_time(to_date('01.01.70','dd.mm.rr')+(utc_in/(60*60*24))
	,'GMT',timezone_abbrev);
end;
/

Function created.

(Proof of concept)
SQL> select to_char(CONVERT_UTC_DATE(1145733907,'MDT'),'dd-MON-yyyy hh24:mi:ss') from dual;

TO_CHAR(CONVERT_UTC_
--------------------
22-APR-2006 13:25:07

And again, the converse:

CREATE OR REPLACE FUNCTION CONVERT_DATE_UTC
	(TIME_STAMP IN DATE, TIMEZONE_ABBREV in VARCHAR2)
   RETURN NUMBER
IS
BEGIN
   return round((new_time(TIME_STAMP,timezone_abbrev,'GMT') -
               to_date('01-01-1970','mm-dd-yyyy'))*(24*60*60));
END;
/

Function created.

(Proof of concept)
SQL> select CONVERT_DATE_UTC(sysdate,'MDT') from dual;

CONVERT_DATE_UTC(SYSDATE,'MDT')
-------------------------------
                     1145733907

Try these code variations for your time zone and tell us if this resolves your original run-time errors.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[MrHelpMe]

O.k.
Still can't believe one line of code to transform epoch into human readable time This is what I have been looking for everywhere. Thanks for this.

The sad part is that I I tried what you suggested SantaMufasa and still receiving
ORA-01830: date format picture ends before converting entire input string from my original post.

Here is my code(well with your function included)

SELECT case_id_ FROM hpd_helpdesk
where convert_utc_date(arrival_time, 'MDT') between to_char(sysdate-to_number(to_char(sysdate,'D'))-7+1,'DD-MON-YYYY hh:mi:ss')
and to_char(sysdate-to_number(to_char(sysdate,'D')),'DD-MON-YYYY hh:mi:ss');

 

[SantaMufasa]

MrHelpMe,

Your code in your WHERE clause is far more complicated than it needs to be. Presently your WHERE code, in order of execution, says:

1) Convert "arrival_time" from "epoch" (Universal Time Co-ordinated [UTC]) into a standard Oracle Date/Time in Mountain Daylight Timezone. (This part is fine)

2) Isolate the number of the current date's Day-of-the Week ["...to_char(sysdate,'D')..."]. For today (Sunday) that means Isolate "1", since Sunday is day number "1" of the week, then subtract 7 and add 1: "1-7+1 = -5"

3) Subtract that result (in this case, subtract -5, which effectively adds 5 days to today) from the current date, and translate the resulting date (28-APR-06) into a character string that (as of this moment) looks like this: "28-APR-2006 10:58:18". (Notice that the result does not show that the time is A.M. or P.M.)

4) Again, isolate into a character string, the current date's Day-of-the-Week number ("1" = Sunday on my machine). Translate the charater "1" into a number and subtract 1 from the current date (yielding "22-APR-2006 10:58:18").

5) Lastly, display all rows that are ">= (the character string) '28-APR-2006 10:35:18' " and "<= (the character string) '22-APR-2006 10:37:50' ". Can you suggest any character value that could result in a TRUE condition? I can't...What string value can be ">= 28..." while at the same time is "<= 22..."

I would be willing to bet a month's income that this WHERE clause is NOT what you intend. Keep in mind that in Oracle, you need not/should not convert DATE/TIME expressions into character strings to do DATE comparisons. If you can put into simple, non-technical terms what DATE values you want your "arrival_time" to be "...BETWEEN...", then we can suggest a highly simplified WHERE clause to do what you want.

The fact that this WHERE clause doesn't do what you really want (i.e., logic error) does not explain why you are receiving the run-time error, "ORA-01830: date format picture ends before converting entire input string". This usually means that your data do not match your DATE-format mask.

To help troubleshoot this situation, could you please post what I asked for in my "22 Apr 06 12:43" post (but haven't seen yet)...I need you to post the results (from the SQL*Plus prompt) of this command: "describe hpd_helpdesk".

I also need to see some actual data in your "hpd_helpdesk" tables, so could you please post the results of this query:

SELECT case_id_, arrival_time
  from hpd_helpdesk
 where rownum <= 5;

So, please post the items that I request, above.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[MrHelpMe]

Below is what you requested SantaMufusa

describe hpd_helpdesk;

Name Null? Type
----------------------------------------
CASE_ID_ NOT NULL VARCHAR2(15)
SUBMITTED_FOR VARCHAR2(30)
ARRIVAL_TIME NOT NULL NUMBER(15)
ASSIGNEE_LOGIN_NAME VARCHAR2(30)
LAST_MODIFIED_BY NOT NULL VARCHAR2(30)
MODIFIED_TIME NOT NULL NUMBER(15)
STATUS NOT NULL NUMBER(15)
SUMMARY NOT NULL VARCHAR2(128)
ASSIGNEE_GROUP VARCHAR2(255)
INSTANCEID VARCHAR2(38)
CATEGORY VARCHAR2(60)
TYPE VARCHAR2(60)
ITEM VARCHAR2(60)
DEPARTMENT VARCHAR2(60)
SITE VARCHAR2(60)
REGION VARCHAR2(60)
ZTMPKEYWORD VARCHAR2(38)

Hpd_Helpdesk data

CASE_ID_ SUBMITTED_FOR ARRIVAL_TIME STATUS
--------------- ------------------------------ ------------HD0000000004863 mphillips 984430674 5
HD0000000004864 abelsher 984431202 5
HD0000000004865 jwhitney 984433012 5
HD0000000004866 vharris 984433058 5

Now to answer another part of your question, "If you can put into simple, non-technical terms what DATE values you want your "arrival_time" to be "...BETWEEN...", maybe I can do so by just describing/ outlining what the report will look like and then defining the terms

Period Totals Tickets Closed
-----------------------------------------------------
Current Week 25
Year to Date 252
Last 12 Months 676

That is one part of the report but I think I can figure out the rest if I get this going

Some definitions
Current Week = One week back from current week starting on Sat and ending on Sunday. So today is April 24, 2006, one week back Sat-Sun would be between Sunday April 16, 2006 - Sat April 22, 2006.

Year to date would be between Current year date starting Jan 1, 2006 - present day.

Last 12 months = We are currently in April so count 12 months back from now. So 12 months from current(april 2006) would start from May 2005.
Please let me know if I missed/forgot something. Thanks again.

 

[SantaMufasa]

MrHelpMe,

To produce the output you specify, you can use this code:

col a heading "Period Totals" format a15
col b heading "Tickets|Closed" format 99,999
col c noprint
select a, b, c
from 
(select 'Current Week' a,count(*) b, 1 c from hpd_helpdesk
 where status = 5
   and convert_utc_date(ARRIVAL_TIME,'MDT')
       between trunc(sysdate,'W')-6
           and trunc(sysdate,'W'))
union
(select 'Year to Date', count(*), 2 from hpd_helpdesk
 where status = 5
   and convert_utc_date(ARRIVAL_TIME,'MDT')
       between trunc(sysdate,'Y') and sysdate)
union
(select 'Last 12 Months', count(*), 3 from hpd_helpdesk
 where status = 5
   and convert_utc_date(ARRIVAL_TIME,'MDT')
       between add_months(trunc(sysdate,'MM'),-11) and sysdate)
order by 3
/

                Tickets
Period Totals    Closed
--------------- -------
Current Week         25
Year to Date        252
Last 12 Months      676

Using the formats that you posted and data in the format that you asserted, I did not encounter the error that you did. In any case, try the code that I posted and let's see what results you get.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[MrHelpMe]

Besides thank you 1 million times it works great. It seems like I have much reading to do on the powers of sysdate and trunc. As for using the union I was planning on doing this in case statements. Do you see a problem?

Also, since I realized I did not mention it but you did it your prior posts, does this account for AM and PM and does it capture data for the whole day. What I mean by this is for instance the current week, does this start at Sunday April 16, 2005 at 12:00 AM and end at Sat April 22, 2006 11:59:59 P.M so that data does not get missed since remedy is Date and Time Specific.
Thanks again SantaMufusa.

 

[SantaMufasa]

Actually, MrHelpMe, you can confirm what each function's behaviour does by doing a SELECT with a TO_CHAR(...). Following is code that confirms the outcome of your inquiry. To produce the results that you wanted (i.e., "Sat April 22, 2006 11:59:59 P.M"), I truncated SYSDATE to the week boundary ("midnight on Saturday morning"), then added 1 full day (making it Sunday April 23), then subtracted one second (i.e., "-(1/24/60/60)"). Here are the outcomes of the truncations, operations, and TO_CHAR functions:

select to_char(trunc(sysdate,'W')-6,'Dy dd-mon-yyyy hh24:mi:ss') Sunday,
       to_char(trunc(sysdate,'W')+1-(1/24/60/60),  'Dy dd-mon-yyyy hh24:mi:ss') Saturday
from dual;

SUNDAY                   SATURDAY
------------------------ ------------------------
Sun 16-apr-2006 00:00:00 Sat 22-apr-2006 23:59:59
****************************************************************************************

Per your clarification, then, your corrected query code (appearing in bold)should read:

col a heading "Period Totals" format a15
col b heading "Tickets|Closed" format 99,999
col c noprint
select a, b, c
from 
(select 'Current Week' a,count(*) b, 1 c from hpd_helpdesk
 where status = 5
   and convert_utc_date(ARRIVAL_TIME,'MDT')
       between trunc(sysdate,'W')-6
           and trunc(sysdate,'W')[b]+1-(1/24/60/60)[/b])
union
(select 'Year to Date', count(*), 2 from hpd_helpdesk
 where status = 5
   and convert_utc_date(ARRIVAL_TIME,'MDT')
       between trunc(sysdate,'Y') and sysdate)
union
(select 'Last 12 Months', count(*), 3 from hpd_helpdesk
 where status = 5
   and convert_utc_date(ARRIVAL_TIME,'MDT')
       between add_months(trunc(sysdate,'MM'),-11) and sysdate)
order by 3
/

                Tickets
Period Totals    Closed
--------------- -------
Current Week         25
Year to Date        252
Last 12 Months      676

If any of my code is not intuitive or you need clarifications of the code's behaviours/purposes/et cetera, please post your inquiry(ies) here.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[MrHelpMe]

Thanks.