select current_time, current_utctime from dummy;
16:07 16:07
/hana/shared/HXE/global/hdb/custom/config/indexserver.ini
/hana/shared/HXE/global/hdb/custom/config/DB_HXE/indexserver.ini
/hana/shared/HXE/exe/linuxx86_64/HDB_2.00.040.00.1553674765_c8210ee/config/indexserver.ini
ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini','SYSTEM') SET ('global','timezone_default_data_schema_nane') = 'SYSTEM' WITH RECONFIGURE;
ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini','SYSTEM') SET ('global','timezone_dataset') = 'sap' WITH RECONFIGURE;
ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini','SYSTEM') SET ('global','timezone_default_data_client_name') = '001' WITH RECONFIGURE;
[global]
timezone_default_data_schema_name = SYSTEM
timezone_default_data_client_name = 001
timezone_dataset = sap
select
to_varchar(utctolocal(utctime,'EST'),'DD-MON-YYYY HH24:MI') "LOCTIME",
UNIT1 "UNIT_1_MWH", UNIT2 "UNIT_2_MWH", UNIT2 "UNIT_2_MWH"
from PlantMWhView
where utctolocal(utctime,'EST') > '2020-11-01 00:00:00'
and utctolocal(utctime,'EST') <= '2020-11-02 00:00:00'
and plant = 29
order by utctime;
LOCTIME UNIT_1_MWH UNIT_2_MWH UNIT_3_MWH
01-NOV-2020 01:00 48 67 98
01-NOV-2020 01:00 50 64 96
01-NOV-2020 02:00 46 62 84
01-NOV-2020 03:00 40 61 76
01-NOV-2020 04:00 38 61 72
01-NOV-2020 05:00 41 60 68
01-NOV-2020 06:00 43 61 67
01-NOV-2020 07:00 47 62 69
01-NOV-2020 08:00 48 64 71
01-NOV-2020 09:00 51 65 72
01-NOV-2020 10:00 55 66 72
01-NOV-2020 11:00 58 67 72
01-NOV-2020 12:00 60 70 72
01-NOV-2020 13:00 63 72 75
01-NOV-2020 14:00 65 74 78
01-NOV-2020 15:00 67 73 83
01-NOV-2020 16:00 64 72 85
01-NOV-2020 17:00 64 71 87
01-NOV-2020 18:00 60 71 94
01-NOV-2020 19:00 59 70 93
01-NOV-2020 20:00 57 69 96
01-NOV-2020 21:00 52 70 97
01-NOV-2020 22:00 51 71 98
01-NOV-2020 23:00 50 69 97
02-NOV-2020 00:00 49 68 96
[global]
timezone_default_data_schema_name = SYSTEM
timezone_default_data_client_name = 002
timezone_dataset = sap
insert into TTZZ values ('002','EST','M0500','USA','X');
insert into TTZR values ('002','M0500','050000','-','X');
insert into TTZDV values ('002','USA','1900','04','1','1','030000','10','1','5','030000');
insert into TTZDV values ('002','USA','2007','03','1','2','030000','11','1','1','030000');
insert into TTZD values ('002','USA','010000','X');
create function my_utctolocal(utctime timestamp, timezone nvarchar(6), timezone_dataset nvarchar(8))
returns loctime timestamp, he char(2), dst char(1)
language sqlscript reads sql data as
begin
declare last timestamp;
loctime := utctolocal(utctime,timezone,timezone_dataset);
he := lpad(to_varchar(hour(ADD_nano100(utctolocal(utctime,timezone,timezone_dataset),-1))+1),2);
-- If the hour just before this one has the same local time, then this is the 2nd DST hour
select utctolocal(add_seconds(utctime,-60*60),timezone,timezone_dataset) into last from dummy;
if :last = :loctime
then
dst := '2';
else
dst := ' ';
end if;
end;
create function my_localtoutc(loctime timestamp, timezone nvarchar(6), timezone_dataset nvarchar(8), dst char(1))
returns utctime timestamp
language sqlscript reads sql data as
begin
declare next timestamp;
utctime := localtoutc(loctime,timezone,timezone_dataset);
-- If the next local hour is the same as this one, and dst = 2, then return the next hour
select utctolocal(add_seconds(utctime,60*60),timezone,timezone_dataset) into next from dummy;
if :next = :utctime and dst = '2'
then
utctime := next;
end if;
end;
select
my_utctolocal(utctime,'EST','sap').he "HE",
my_utctolocal(utctime,'EST','sap').dst "DST",
UNIT1 "UNIT_1_MWH", UNIT2 "UNIT_2_MWH", UNIT2 "UNIT_2_MWH"
from PlantMWhView
where my_utctolocal(utctime,'EST',’sap’) > '2020-11-01 00:00:00'
and my_utctolocal(utctime,'EST',’sap’) <= '2020-11-02 00:00:00'
and plant = 29
order by utctime;
HE DST UNIT_1_MWH UNIT_2_MWH UNIT_3_MWH
01 48 67 98
02 50 64 96
02 2 46 62 84
03 40 61 76
04 38 61 72
05 41 60 68
06 43 61 67
06 47 62 69
08 48 64 71
09 51 65 72
10 55 66 72
11 58 67 72
12 60 70 72
13 63 72 75
14 65 74 78
15 67 73 83
16 64 72 85
17 64 71 87
18 60 71 94
19 59 70 93
20 57 69 96
21 52 70 97
22 51 71 98
23 50 69 97
24 49 68 96
# Rule NAME FROM TO TYPE IN ON AT SAVE LETTER/S
Rule US 1974 only - Jan 6 2:00 1:00 D
Rule US 1975 only - Feb lastSun 2:00 1:00 D
Rule US 1976 1986 - Apr lastSun 2:00 1:00 D
Rule US 1987 2006 - Apr Sun>=1 2:00 1:00 D
Rule US 2007 max - Mar Sun>=8 2:00 1:00 D
Rule US 2007 max - Nov Sun>=1 2:00 0 S
TTZZ values ('002','EST','M0500','USA','X');
TTZR values ('002','M0500','050000','-','X');
TTZDV values ('002','USA','2007','03','1','2','030000','11','1','1','030000');
TTZD values ('002','USA','010000','X');
Zone UTCStart UTCStop DST Offset ZoneAsc
372 Mar 10 2019 07:00 Nov 3 2019 06:59:59.999 -240 EDT
372 Nov 3 2019 07:00 Nov 3 2019 07:59:59.999 2 -300 EST
372 Nov 3 2019 08:00 Mar 8 2020 06:59:59.999 -300 EST
372 Mar 8 2020 07:00 Nov 1 2020 06:59:59.999 -240 EDT
372 Nov 1 2020 07:00 Nov 1 2020 07:59:59.999 2 -300 EST
372 Nov 1 2020 08:00 Mar 14 2021 06:59:59.999 -300 EST
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
7 | |
6 | |
5 | |
4 | |
4 | |
4 | |
4 | |
4 | |
3 | |
3 |