How to convert QINFO data?

[PK]

Need help with converting call concentrator data into usable form.

I want to calculate the number of calls entering queue "queue a" on April 3, 2006 using qinfo table stored in Oracle database.

I was able to get this far:

select to_char( to_date('01011970','ddmmyyyy') + 1/24/60/60 * STartTIME , 'ddmonyyyy hh24:mi:ss') field1 from QINFO where queuedbid = 1231 and field1 > '4/18/2006';

where queuedbid corresponds to dbid of queue in cfg_dn. This is not working well because I need to take into account our timezone (UTC+3) and also need to specify "between". Can someone please help me with this?

Thank you very much!

[Tony Tillyer]

Try this;

select to_char( to_date('01011970','ddmmyyyy') + (1/24/60/60 * STartTIME 3600), 'ddmonyyyy hh24:mi:ss') field1 from QINFO where queuedbid = 1231 and (field1 > '4/18/2006') and (field1 < '4/20/2006')

Your date converstion relies on the number of seconds past 01/01/1970, which can be amended to include and extra 3 hours and your specified date is not a range hence is the same as requesting any time/date for data which arrived only after 18th April 2006;

Change A = " 3600" which adds 3 hours to the equation (UTC+3)
Change B = "and (field1 < '4/20/2006')" which ensures query is constrained to just one day

Let me know how you get on, since I do not have anything to check the syntax of this query.

Tony

[Tony Tillyer]

Spot the deliberate mistake!

...I wrote 3600 instead of + 3600

Tony

[Vic]

How, Tony, thank you for the deliberate mistake.
Question does it take into account a leap year?

I thought there was a problem with conversion from unit time because a of leap year.

[Tony Tillyer]

You're right Vic perhaps a calculation of hte number of seconds past 1/1/70 is not the way to go... And that would be dependant on your dB version.

Is it SQL, Oracle or DB2?

[Tony Tillyer]

Vic,

Apparently, UNIX epoch date/time in seconds does not make use of leap seconds, so there should be no issue.

Tony

[Vic]

Tony,

thank you! I will use this as well. I need to show times for SCDR table and this unix time really throws things off.

Vic

[Tony Tillyer]

Better to make sure yourself, Vic apply the equation to a known timeframe and check to see that the conversion is correct.

I'd also be interested to know if it works out OK.

Tony

[Kevin]

For the record...
If you're working with Sybase or MSSQL, you should be able to use the dateadd() function, adding the seconds to '01/01/1970 0:00' and come up with the correct response.
Of course, if you're capturing the times across multiple time zones, it's another issue...

[Vic]

I am not very good at Oracle nor did I have time to make it nice, but this works:

select distinct (to_char(connid)),status, duration, to_char((to_date('09 01011970','hh24 ddmmyyyy') + 1/24/60/60 * STartTIME),'ddmmyyyy hh24:mm:ss'),starttime from QINFO where to_date('09 18012006','hh24 ddmmyyyy') to_timestamp(to_date('09 01011970','hh24 ddmmyyyy') + 1/24/60/60 * STartTIME) between interval '0' day and interval '1' day and status = 3 and queuedbid = 1212;


It shows all callids for 1/18/2006 for queue with dbid 1212.

Note: Timezone was taken into account by replacing to_date('01011970','ddmmyyyy') with to_date('09 01011970','hh24 ddmmyyyy')

Also note: I think it totally messes up the minutes part... for some inate reason the minute part for all the calls is shown as 04... Weird... The date though works.

It is not perfect, but it will get you what you want.