Converting UTC Timestamp to locale

[Jens Hoffrichter]

Hi,

I have a problem regarding the conversion of a timestamp to my current
locale.

I have a table which stores the timestamps in the Unix timestamp
format (seconds since 1/1/1970) to save space and gain on performance
while searching through the table. Now I want to get a statistic how
many rows are there from per hour.

I try to use a select with a groupby clause like:

select hour(unixtimestamp(timestamp)) as hour,count(id) from
data.clicks where timestamp between ? and ? group by
hour(unixtimestamp(timestamp))

Unixtimestamp is a custom function which converts a unix timestamp to
a DB2 timestamp (found in this group), looking like this:

create function UNIXTIMESTAMP(TS bigint)
returns timestamp
language sql
contains sql
no external action
deterministic
return TIMESTAMP('1970-01-01-00.00.00') + (ts / 1000) SECONDS;

The time"slot" which I want to look at is configured by a web
interface and displays the time in the local (german) time. I convert
this time to a Unix timestamp put it into the select clause, it
selects the correct slot I want to look at.

Now the results should be presented on a Web-Interface, but the
problem is that the grouping is done with the UTC representation of
the DB2 timestamp. So if I select to display values from 9/2/2002 0:00
AM to 9/2/2002 23:59 AM, I get results from 10 PM the day before to 9
PM of the actual selected day.

But I cannot add 2 hours every time because of daylight saving issues.

Has anyone an idea how to convert the (UTC) timestamp to a local
timestamp before it putting into the group by clause? I haven't found
a hint how to do this. I have the possibilities to do this in the
application or in a java external function, but I fear the overhead
(in the application, the development overhead, in the external
function the database load) by doing it this way.

I would really appreciate if someone has an idea how to do this.

CU all,
Jens

P.S.: If possible, CC me at an answer to this question, thanks in
advance!

[Christian Maslen]

Hi Jens,

Have a look at the CURRENT TIMEZONE special register.

select current timestamp as "Local Time"
, current timestamp - current timezone as "UTC Time"
, timestamp(generate_unique()) as "UTC Time (frm generate_unique)"
, timestamp(generate_unique()) + current timezone as "Local Time
(gen unique again)"
from (values(1) ) as t;

Christian.

[Paul Vernon]

Is this what you require?

CREATE FUNCTION F.GMT2BST (X TIMESTAMP)
RETURNS TIMESTAMP
LANGUAGE SQL CONTAINS SQL NO EXTERNAL ACTION DETERMINISTIC RETURN
CASE WHEN X >=( TIMESTAMP('0001-03-01-01.00.00')
+ (YEAR(X) - 1) YEARS + (31 - DAYOFWEEK(
TIMESTAMP('0001-03-31-00.00.00')
+ (YEAR(X) - 1) YEARS) ) DAYS) AND X < (
TIMESTAMP('0001-10-01-01.00.00')
+ (YEAR(X) - 1) YEARS + (31 - DAYOFWEEK(
TIMESTAMP('0001-10-31-00.00.00')
+ (YEAR(X) - 1) YEARS) ) DAYS ) THEN X + 1 HOUR ELSE X END
@
COMMENT ON FUNCTION F.GMT2BST IS
'Adds an hour to a timestamp if it lies within Europen daylight
saving time'
@

Regards
Paul Vernon
Business Intelligence, IBM Global Services

[JEA]

Having a similiar problem with a PLI program. PGM is embedded with a
clist that generates the RECOV.JCL jcl according to the
RCVTIME(xxxxxxxxxxxx-0500). I have been able to add the UTC to the clist
but now, I need to be able to change the UTC when day light savings
times occurs...

DCL, 1 OUTREC1, 2 F1 CHAR(18) INIT('GENJCL.RECOV DBD('), 2 DBN CHAR(8),
2 F2 CHAR(6) INIT(')'), 2 RCVTIME CHAR(8) INIT('RCVTIME('), 2 DTS
CHAR(12) INIT(''), 2 UTCDIFF CHAR(05) INIT('-0500'), 2 RCV_PAREN CHAR(2)
INIT(')'), 2 JOB CHAR(6) INIT('JOB'), 2 DASH CHAR(1) INIT(''), 2 F3
CHAR(14) INIT('');

Anyone have any ideas?

Thanks,

JEA

--
www.MainFrameForum.com - USENET Gateway