Keresés

Random ötletem: a DBTIMEZONE megegyezik a to_date(SYSTIMESAMP,'TZH:TZM')-vel, akkor nincs DST, egyébként van. Rendkívül tudományos...

Off: Már az sem triviális, hogy a hét napját megkapjam, ugyanis a to_date('D') esetén territory-függő, hogy mit jelentenek az 1-7 számok. A megfejtés az, hogy lekérdezzük egy rögzített hétfői dátumra ugyanezt, és abból kalkulálunk.

a data format mask-ban a tzd field mondja meg hogy az adott dátum az téli vagy nyári.

a systimestamp az timestamp with timezone tipust ad vissza. tehát lesz benne időzóna, de nem a session-é, hanem a database timezone. 

select to_char(from_tz ( cast(to_date('2015 01 01','yyyy mm dd') as timestamp) ,'europe/budapest') , 'TZD, TZR') from dual;
select to_char(from_tz ( cast(to_date('2015 07 01','yyyy mm dd') as timestamp) ,'europe/budapest') , 'TZD, TZR') from dual;

ez CET/CEST-et ad

select to_char(from_tz ( cast(to_date('2015 01 01','yyyy mm dd') as timestamp) ,'europe/moscow') , 'TZD, TZR') from dual;
select to_char(from_tz ( cast(to_date('2015 07 01','yyyy mm dd') as timestamp) ,'europe/moscow') , 'TZD, TZR') from dual;

ez viszont mindenképp MSK-t, mert az oroszoknál nincs óraátállítás.

select to_char(from_tz ( cast(to_date('2000 01 01','yyyy mm dd') as timestamp) ,'europe/moscow') , 'TZD, TZR') from dual;
select to_char(from_tz ( cast(to_date('2000 07 01','yyyy mm dd') as timestamp) ,'europe/moscow') , 'TZD, TZR') from dual;

ez meg MSK/MSD-t, mert 2000-ben még volt. frankó mi?

Vajk

Pótkérdés: le lehet-e kérdezni , mondjuk a systimestamp-ból, hogy van-e nyári időszámítás?

sajnos ki lehet. létezik időzóna nélküli timestamp. annak is van zónája, csak az adatbázis nem tud róla hogy mi az, nekünk kell megmondani.

pl ez a két utc time ugyarra a local time-ra konvertálódik. a tzd format maskkal lehet megtudni hogy az első vagy a másiodik 2:30 aznap.

select from_tz(to_timestamp ('2015 10 25 1:30','yyyy mm dd hh24:mi'), 'UTC') at time zone 'europe/budapest' from dual;
select from_tz(to_timestamp ('2015 10 25 0:30','yyyy mm dd hh24:mi'), 'UTC') at time zone 'europe/budapest' from dual;

select from_tz(to_timestamp ('2015 10 25 0:30','yyyy mm dd hh24:mi'), 'UTC') at time zone 'CET' from dual;

25-OCT-15 02.30.00.000000000 AM CET

select from_tz(to_timestamp ('2015 10 25 0:30','yyyy mm dd hh24:mi'), 'UTC') at time zone 'CEST' from dual;

ORA-01882: timezone region  not found

Vajk

kicsit kifejthetnéd :-)

Timestampet, időt vagy  mint system időt tudod megadni, vagy karakterből kell konvertálnod. Akkor pedig az időzónát nem lehet kikerülni. Belül úgyis anélkül van tárolva.  Az ismétlődő óra végül is két két zóna névvel jelentkezik, egyszer a téli, egyszer anyári időszámítás szerint..

SELECT * from V$TIMEZONE_NAMES WHERE TZNAME LIKE '%Berlin%';

Ha kihagyod a timezone adattípusokat, akkor is használhatod az adatbázis naptárát.

Pl:

create table test (a timestamp(9), b varchar2(200));
insert into test values (systimestamp, to_char(systimestamp));

select a,b,
from_tz(a,'europe/budapest') elhitetjuk_hogy_helyi_ido,
from_tz(a,'europe/budapest') at time zone 'UTC' utc_ben
from test;

Ez egy időzóna nélküli oszlopban eltesz egy dátumot. Aztán megmondja róla hogy budapesti idő, aztán konvertálja UTC-re.

mostmár eldugulok.

Vajk

A timestamp jo lenne, akkor az adatbazis nem foglalkozik az idozonakkal egyaltalan. viszont ha a kod maga cet/cest idot tesz bele, akkor meg vagy love.

a cet/cest szerinti idovonal nem folytonos

 - pl idén márc 29-edikén nem volt 2:30 am egyáltalán, sosem.

 - ősszel még szarabb, mert kétszer lesz október 25 2:30 am, és nincs mód annak jelzésére hogy az elsőre vagy a másodikra gondolsz. pl telefonomon volt naptár figyelmeztetés, és éjjel kétszer jelzett :-)

az egész időzóna fogalom nagyon macerás. az én felfogásom a következő (eddig bejött)

az amerikaiak szerint vannak időzónák, amiknek az offsetje fix. óraátállításkor az adott terület időzónát vált. ebben a modellben van CET ami UTC+1 és van CEST ami UTC+2. és a január 1. 14:21 CEST az egy érvénytelen dátum, mert CEST nincsen januárban sehol. nem egy rossz modell, pl megoldja a fenti problémát hogy ősszel kétszer van október 25 2:30, az egyik lesz az október 25 2:30 CEST, a másik meg az október 25 2:30 CET.

az európaiak szerint vannak időzónák, amiknek a definíciójában az offset értéke megváltozik az óraátállításkor. nem is szívesen használnak timezone neveket, inkább a timezone region notation passzol ide. ez szerint nálunk europe/budapest zóna van, ami az év egy részében UTC+1, más részében UTC+2. ami a fenti problémákat okozza.

kis kitérő, ha már belejöttem: mivel az időzónák határai nem igazodnak a közigazgatási hierarchiához, nem lehet egy adott zónához mondani egy teljesen fedő közigazgatási/politikai nevet. ezért a zónáknak "fővárosai" meg alsóbbrendű jellemző városai vannak. Jó kis szopás volt mostanában, hogy a tőlünk egyel keletre levő zóna fővárosa Szevasztopol volt, és a románok meg egy csomóan az europe/sevastopol-t használtál. Aztán amikor a Krím az oroszokhoz került, ami más időzóna és ráadásul nincs benne téli/nyári állítás, hirtelen egy csomó mindenkinek furán járt az órája. Ráadaásul egy IT rendszerben sok rétegben van naptár implementáció, csak mind ugyanabból a definícióból készül, ezért konzisztens. Namost ahogy szépen nem egyszerre megjöttek a frissítések arról hogy az europe/sevastopol mást jelent mostantól, baromi inkonszisztens lett midnen. Egyik héten frissult a java, másik héten a windows, harmadikon a outlook, valamikor a naptár szerver, stb.

szóval loggolásnak az lenne a legjobb ha UTC-ben menne, mert az folytonos. ha sokat kell helyi időben mutatni, akkor meg timestamp with local timezone, az utc-ben (mondjuk, persze valami internal formatban) tárolódik, és session timezoneban olvasódik, íráskor is session timezoneról automatikusan konvertálódik. mint a charset.

Vajk

> a napló táblád milyen adattípus akkor? timestamp with local timezone

Csak TIMESTAMP(6). Tartalma localtime: CET/CEST.

> TIMESTAMP [(fractional_seconds_precision)]

fractional_seconds_precision is optional and specifies the number of digits in the fractional part of the SECOND datetime field. It can be a number in the range 0 to 9. The default is 6.

Igen, erre szaladtam rá, a PL/SQL-ben a SYSTIMESTAMP-ból indultam ki, ott nyilván 9 a default.

> ha timestamp with local timezone, akkor minden meg van oldva. beállítod a session timezone-ot utc-re, és nincs izgalom csak júni 30adikán.

Na most elgondolkoztam, hogy mi is a szerver beállítása

select sessiontimezone, dbtimezone, sysdate, systimestamp, sys_extract_utc(systimestamp) from dual;

SESSIONTIMEZONE +02:00
DBTIME +01:00

SYSDATE 20150428.101923
SYSTIMESTAMP  20150428.101923.876805 +02:00
SYS_EXTRACT_UTC(SYSTIMESTAMP) 20150428.081923.876805

Ha ebből az következne, hogy a 'timestamp with local timezone' mindig CET-et tárolna, vagyis nem lépegetne téli/nyári időszámítás-váltáskor, akkor valóban jobb lenne... na majd megnézem, erre való a DUMP.

ha timestamp with local timezone, akkor minden meg van oldva. beállítod a session timezone-ot utc-re, és nincs izgalom csak júni 30adikán.

TIMESTAMP [(fractional_seconds_precision)]

fractional_seconds_precision is optional and specifies the number of digits in the fractional part of the SECOND datetime field. It can be a number in the range 0 to 9. The default is 6.

szóval a default az 6, de amikor deklarálod megadhatod mennyit tároljon.

a napló táblád milyen adattípus akkor? timestamp with local timezone? 

Most éppen az őszi óravisszacsavarás miatt aggódom: egy TIMESTAMP szerint növekvő sorrendben kellene olvasni egy napló-táblát -- sajnos a TIMESTAMP az LOCALTIME-ban értendő, ezért olyasmit találtam ki, hogy ha 10. hónap van, és vasárnap, és nap>=25, és UTC between 00:00 and 02:00 akkor várjunk, míg UTC 02:00 (plusz egy pici) lesz.

Ugyanis, ha jól látom UTC 00:00-00:59 = CEST 02:00-02:59, UTC 01:00-01:59 = CET 02:00-02:59. (Természetesen az átfedő intervallumok miatt a rekordok összekeverednek, ezen nem lehet segíteni, én csak azon aggódom, hogy a program ne olvasson semmit kétszer, de ne is hagyjon ki semmit.)

Rant: remek, hogy ilyen felbontása van, csak kár, hogy a timezone nélküli timestampnél (pl. sys.aud$) még az óra sem biztos.

Kicsit összekavartam magam a to_date (timestamp,'FF') -vel, illetve annak a hosszával. Úgy tűnik nekem, hogy 'símán SQL-ből' 6-jegyű lesz a 'FF' (microsecund), PL/SQL-ből 9-jegyű (nanosecund). Ilyesmi a kód:

#1 SELECT TO_CHAR (f_ts,     'YYYYMMDDHH24MISSFF') FROM table;

#2 ts_client_utc := to_timestamp (ts_server_utc, 'YYYYMMDDHH24MISSFF');

Ilyesmi a kimenet:

#1 c_ts ='20150425.204737.194564' (len=20=8+6+6)

#2 server UTC: 20150427.191158.442137000 (ind=0, len=23=8+6+9)

Késöbb: na jó, asszem az lesz a legjobb explicite kíírom, hogy FF9...

értelek. nyilván ilyen listaggot is tudnék írni, de tény hogy a partition clause-os rendezés sokkal rugalmasabb és esélyes hogy hatékonyabb is.

viszont a rownummal nem kell bűvészkedni, a TK_aggregalo(szoveg) over(PARTITION BY id ORDER BY rendezo_oszlop DESC) minden id-re ugyanazt adja. ha id-nként egy sort akarsz csak, akkor max group by a szokás.

SELECT user_id
  , max(TK_aggregalo(szoveg) over(PARTITION BY id ORDER BY rendezo_oszlop DESC)) aggr
 FROM tabla a
 group by id;

Vajk

Azt hiszem, túl egyszerű példán akartam levezetni, miért kell az analitikus rész.

create table tabla(

user_id number,

szoveg varchar2(100),

rendezo_oszlop number

);

1,a,3

1,b,1

1,c,3

Ezt gyűjtsd fel úgy, hogy a szoveg oszlopot konkatenálod, rendezve a rendezo_oszlopra.

Tudom, erre jó még a listagg, de tegyük fel, hogy a konkatenált szöveg meghaladja a 4K-t (ez volt az alap felvetés, és írtam, hogy az egyik member-es részt ki kell egészíteni substr-el).

Így lesz belőle ez:

SELECT user_id
      ,aggr
  FROM (SELECT user_id
              ,aggr
              ,row_number() over(PARTITION BY id ORDER BY length(aggr) DESC) rn
          FROM (SELECT user_id
                      ,TK_aggregalo(szoveg) over(PARTITION BY id ORDER BY rendezo_oszlop DESC) aggr
                  FROM tabla a))
 WHERE rn = 1;

dehát mi szerint akarod rendezni? egy értékre csinálsz aggregációt, azt nincs mi szerint rendezni, csak önmaga szerint. max az a kérdés hogy melyik irányba meg a nullok. de senki nem tiltja hogy a függvényednek két paramétere legyen.

Vajk

Jogos, két megjegyzéssel. :)

Egy, én voltam a béna, mert az alap verzióm nem ez volt, hanem az eredeti TK link: http://www.sqlsnippets.com/en/topic-11591.html

A másik, hogy így valóban rendezett, viszont mindig aszerint, ahogy a terminate-ben felvetted, én pedig tetszőlegesen akartam variálni, és az analitikussal bármelyik oszlopra tudod rendezni.

De az is igaz, hogy ha mindegy, csak a rendezettség számít, akkor az új link bőven lefedi az igényt. :)

a linkelt példában levő aggregate function csinál rendezést a lista elemein. mindig ugyanabban a sorrendben fogja oket visszaadni.

ki is probaltam, mert erdekel:

-- http://www.sqlsnippets.com/en/topic-12089.html

create or replace type varchar2_table_type as table of varchar2(4000) ;
/
create or replace type vstragg_type as object
(

val_table varchar2_table_type,

static function ODCIAggregateInitialize
( sctx in out vstragg_type )
return number ,

member function ODCIAggregateIterate
( self in out vstragg_type ,
value in varchar2
) return number ,

member function ODCIAggregateTerminate
( self in vstragg_type,
returnvalue out varchar2,
flags in number
) return number ,

member function ODCIAggregateMerge
( self in out vstragg_type,
ctx2 in vstragg_type
) return number
);
/

create or replace type body vstragg_type
is

static function ODCIAggregateInitialize
( sctx in out vstragg_type )
return number
is
begin

-- sctx := stragg_type( null ) ; -- deleted

sctx := vstragg_type( varchar2_table_type() ); -- added

return ODCIConst.Success ;

end;

member function ODCIAggregateIterate
( self in out vstragg_type ,
value in varchar2
) return number
is
begin

-- self.string := self.string || ',' || value; -- deleted

self.val_table.extend; -- added
self.val_table(self.val_table.count) := value; -- added

return ODCIConst.Success;
end;

member function ODCIAggregateTerminate
( self in vstragg_type ,
returnvalue out varchar2 ,
flags in number
) return number
is

v_data varchar2(4000); -- added

begin

-- returnValue := ltrim(self.string, ','); -- deleted

for x in -- added
( -- added
select column_value -- added
from table(val_table) -- added
order by 1 -- added, define sort order here
) -- added
loop -- added
v_data := v_data || ',' || x.column_value;-- added
end loop; -- added

returnValue := ltrim( v_data, ',' ); -- added

return ODCIConst.Success;

end;

member function ODCIAggregateMerge
( self in out vstragg_type ,
ctx2 in vstragg_type
) return number
is
begin

-- self.string := self.string || ctx2.string; -- deleted

for i in 1 .. ctx2.val_table.count -- added
loop -- added
self.val_table.extend; -- added
self.val_table(self.val_table.count) := ctx2.val_table(i); -- added
end loop; -- added

-- in 10g this for loop could probably be replaced with the following
-- self.val_table := self.val_table multiset union ( ctx2.val_table ) ;

return ODCIConst.Success;

end;

end;
/

create or replace function vstragg
( input varchar2 )
return varchar2
deterministic
parallel_enable
aggregate using vstragg_type
;
/

drop table test;
create table test (id number, a varchar2(20));
/
insert into test values (1,'alma');
insert into test values (1,'korte');
insert into test values (1,'dio');
insert into test values (1,'szilva');
insert into test values (1,'meggy');
insert into test values (2,'alma');
insert into test values (2,'barack');
insert into test values (3,'barack');
insert into test values (3,'alma');
insert into test values (3,'barack');
insert into test values (4,'szolo');
commit;

select id, vstragg(a)
from test
group by id
order by id;

ID VSTRAGG(A)

-- --------------------------------

1 alma,dio,korte,meggy,szilva
2 alma,barack
3 alma,barack,barack
4 szolo

klafa ha csinálna distinctet is, meg is fogom csinálni, bármikor jól jöhet.

van benne a terminate függvény:

member function ODCIAggregateTerminate

   ( self in vstragg_type ,
   returnvalue out varchar2 ,
   flags in number
   ) return number

ez az ami a tábla elemeiből összekonkatenálja a kimenetet. és ott van benne az order by. és mint fent látszik, működik is.

Vajk 

Javítás...

1, 'a,b,c'

1, 'a,c,b'

1, 'b,a,c'

1, 'b,c,a'

1, 'c,a,b'

1, 'c,b,a'

és

1, 'a'

1, 'a,b'

1, 'a,b,c'

ahol 1 az id oszlop tartalma, a többi a konkatenáció, amit a funkció kiköp a szöveg oszlop alapján.

Csinálj egy temp táblát, id, és szöveg oszloppal. Ha feltöltöd a szöveget és ráengeded ezt a cuccot, akkor nem rendezetten fűzi össze a cuccokat. Ezzel az a gond, hogy ha van három sorod, id = 1, szöveg a, b, c, és ráengeded , akkor a lentiek bármelyikével térhet vissza.

id, 'a,b,c'

id, 'a,c,b'

id, 'b,a,c'

id, 'b,c,a'

id, 'c,a,b'

id, 'c,b,a'

Ha lefuttatod kétszer, más eredményt adhat, és ha ki akarod minuszolni őket, eltérhetnek. Ez nyilván a példában nem gáz, de ha van egy 50 oszlopos select-ed, és hozzányúl, és meg akarod nézni, mi változott, akkor ez bezavarhat. Ezért kell ráhúzni azt az analitikus függvényt egy over résszel, mert ott meg tudod mondani a rendezést,

amiből az lesz (mondjuk rendezve szövegre), hogy:

id, 'a'

id, 'a,b'

id, 'a,b,c'

Ezután már könnyen meg tudod fogni, hogy melyik az a sor, ahol mind össze van fűzve (length az aggregált oszlopra), és ki tudod venni az utolsót, ráadásul mindig a megfelelő sorrendben lesz rendezve, tehát az 50 oszlopos select-et is nyugodtan lehet minuszolni. :)

Persze, ha nem számít a rendezettség, csak az infó kell, akkor ez az egész nem lényeg, elég a link, de nekem pont ez kellett. :))

de ez tök jó! és ez egy aggregate function, nem kell semmi analitikus, mehet a group by es kesz.

Ez az alap:

http://www.sqlsnippets.com/en/topic-12089.html

De ezt fel kell még okosítani, mert ha kifut a 4K-ból, akkor elszáll.

Ha ezt lekezeled, erre mehet kívül majd az analitikus fv a rendezéssel és a végén a szűréssel.

tudsz adni egy linket?

az anal függvény abban különbözik a group függvénytől, hogy előbbi minden sorra ad valamit, a másik meg sorok egy csoportjára.

Vajk

Hali!

Végülis ez vezetett a megoldáshoz. Tom Kyte honlapján volt egy rész, ahol leírt egy karakter aggregálót. Ez működött is, ha egy kicsit átírjuk, csak nem lehetett rendezni az összefűzést.

Viszont ha arra rátettem az analitikus függvényt, akkor nem group by-olta fel, hanem az adott sorhoz elvégezte az összefűzést a megelőző sorokkal. Viszont ebből az is következik, hogy az utolsó sornál már megvan a teljes könkatenáció a megfelelő rendezéssel, így csak kell egy külső select egy row_number-rel és kész. :)

Nekem nincs. A doc szerint lehet analitikus függvényként használni.

tapasznyalat nincs, de engem is érdekel!

Sziasztok!

Van valakinek tapasztalata user-defined aggregator területen? A listagg megadja magát, ha 4K-t eléri az összefűzés. Egy olyan függvényt szeretnék, ami összefűzi a karaktereket, majd a legvégén vágja le 4K-nál, így hibatűrőbb lesz a dolog. Maga az eljárás megvan, de az a gond, hogy míg a listagg rendezett, ez nem az, így nem épp determinisztikus a dolog. Azt szeretném megtudni, hogy lehet-e valahogy rendezni az ilyen típuson belül?

Köszi