1 / 26

Datumsfunktionen von Oracle

Datumsfunktionen von Oracle. ADD_MONTHS Syntax ADD_MONTHS(d,n) Purpose

robert-ward
Download Presentation

Datumsfunktionen von Oracle

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Datumsfunktionen von Oracle ADD_MONTHS Syntax ADD_MONTHS(d,n) Purpose Returns the date d plus n months. The argument n can be any integer. If d is the last day of the month or if the resulting month has fewer days than the day component of d, then the result is the last day of the resulting month. Otherwise, the result has the same day component as d.

  2. Datumsfunktionen von Oracle LAST_DAY Syntax LAST_DAY(d) Purpose Returns the date of the last day of the month that contains d. You might use this function to determine how many days are left in the current month.

  3. Datumsfunktionen von Oracle NEXT_DAY Syntax NEXT_DAY(d, char) Purpose Returns the date of the first weekday named by char that is later than the date d. The argument char must be a day of the week in your session's date language-either the full name or the abbreviation. The minimum number of letters required is the number of letters in the abbreviated version; any characters immediately following the valid abbreviation are ignored. The return value has the same hours, minutes, and seconds component as the argument d.

  4. Datumsfunktionen von Oracle ROUND Syntax ROUND(d[,fmt]) Purpose Returns d rounded to the unit specified by the format model fmt. If you omit fmt, d is rounded to the nearest day. See "ROUND and TRUNC" for the permitted format models to use in fmt.

  5. Datumsformate in Oracle

  6. Datumsfunktionen von Sybase dateadd - adds an interval to a specified date. It takes three arguments-- the datepart, a number, and a date. The result is a datetime value equal to the date plus the number of date parts. select newpubdate = dateadd(day, 21, pubdate) from titles

  7. Datumsfunktionen von Sybase datediff - calculates the number of date parts between two specified dates. It takes three arguments. The first is a date part. The second and third are dates, either datetime or smalldatetime values. The result is a signed integer value equal to date2 - date1, in date parts. select newdate = datediff(day, pubdate, getdate()) from titles

  8. Datumsfunktionen von Sybase datename - produces the specified datepart (the first argument) of the specified date (the second argument) as a character string. Takes either a datetime or smalldatetime value as its second argument. select datename(month getdate())   November

  9. Datumsfunktionen von Sybase datepart - produces the specified datepart (the first argument) of the specified date (the second argument) as an integer. Takes either a datetime or smalldatetime value as its second argument. select datepart(month getdate())   11

  10. Datenkonvertierung Sybase convert (datatype [(length) | (precision[, scale])], expression[, style]) Converts between a wide variety of datatypes and reformats date/time and money data for display purposes.

  11. Auswertung pro Stunde select to_char(datum,'hh24'), count(*) from ta_zeitreihe group by to_char(datum,'hh24') order by to_char(datum,'hh24');

  12. Auswertung pro Wochentag select to_char(datum,'day'), count(*), to_char(datum,'d') from ta_zeitreihe group by to_char(datum,'day'), to_char(datum,'d') order by to_char(datum,'d');

  13. Auswertung pro Tag select to_char(d1.datum, 'yy.mm.dd'), count(z.schluessel) "Anzahl" from ta_dim_zeit_1 d1, ta_zeitreihe z where d1.jahrestag = z.jahrestag (+) group by to_char(d1.datum, 'yy.mm.dd'), d1.jahrestag order by to_char(d1.datum, 'yy.mm.dd');

  14. ta_teilnehmer Ein Eintrag pro Teilnehmer, für jede Antwort eine Spalte 30051980070919811 4 4 1 2 1 1 3 3 2 4 1 01.01.197002.01.19705 4 3 4 2 1 4 4 4 3 11.11.191111.11.191111 2 4 2 3 3 2 2 4 1 1 2 1 3 3 2 1 2 4 4 1 1 27.04.195115.09.19422 2 3 2 2 3 2 1 3 1 3 1 1 4 21.08.195923.06.19591 2 3 4 2 3 3 2 1 1 4 3 4 1 3 1 4 3 2 3 1 1 1 1 31.05.195426.11.19522 2 1 1 3 2 3 3 4 2 4 1 3 2 1 1 2 1 1 2 3 1 1 2 00.00.0000.00.0040 1 4 3 2 4 16.03.4816.03.442 3 4 4 1 3 1 3 4 01.01.195621.09.194125 3 4 4 2 1 3 3 1 4 1 3 3 4 13.06.195419.07.19531 2 1 3 2 3 3 3 2 2 3 4 3 16.06.193620.04.19361 2 3 4 2 3 2 2 3 1 4 4 3 3 3 2 1 3 4 3 4 2 1 2 17.10.195619.10.19641 3 3 3 2 4 1 3 3 2 4 2 4 01.01.0001.01.009 3 3 4 2 3 4 3 1 1 4 4 2 2 2 1 4 1 4 3 4 4 2 3 11.1.453.3.432 4 3 2 1 4 2 4 3 2 4 2 3 1 1 1 1 1 1 17.03.195421.10.19542 1 1 1 2 2 2 2 3 3 4 3 2 23.01.195527.03.1953k.a. 4 1 4 2 4 4 2 2 1 3 3 2 3 4 3 2 1 4 4 4 1 2 4 28.05.195902.05.19502 2 3 3 2 4 2 4 2 1 4 1 3 1 999999999999999999999999 3 3 3 2 3 3 3 2 4 4 1 2 4 1 3 2 2 4 1 2 1 1 1

  15. ta_teilnehmer Testen Sie die folgenden Befehle bitte zuerst an der ca. 100 Einträge großen Ausschnittstabelle! insert into ta_teilnehmer (probant) select distinct probant from ta_zeitreihe; update ta_teilnehmer set e1=(select ergebnis_1*1+ergebnis_2*2+ergebnis_3*3+ergebnis_4*4 from ta_zeitreihe where ta_teilnehmer.probant = ta_zeitreihe.probant and ta_zeitreihe.aufgaben_nr=1);

  16. ta_zeit Ein Eintrag pro Teilnehmer, für jede Antwort die benötigte Zeit 28.01.6131.01.601 04 03 02 02 03 02 01 01 04 00 01 05 02 01 02 02 00 01 00 999999999999999999999999 01 01 01 02 01 01 01 01 01 00 00 01 00 01 00 01 00 00 01 30051980070919811 01 01 01 01 00 01 02 01 01 23.01.5527.03.533 00 00 00 01 00 00 01 00 00 00 01 00 00 -06 01 19.07.196017.12.19591 05 00 01 02 01 02 00 01 03 01 01 01 01 00 01 02 00 00 00 13.06.195419.07.19531 01 00 01 00 00 01 01 01 00 00 12.04.196231.03.19561 01 02 01 02 01 01 00 02 00 00 01 02 02 01 00 05 00 01 00

  17. ta_zeit update ta_zeit z set z2=(select((to_char(zb.datum,'ddd')*24 +to_char(zb.datum,'hh24'))*60 +to_char(zb.datum,'mi')) -((to_char(zv.datum,'ddd')*24 +to_char(zv.datum,'hh24'))*60 +to_char(zv.datum,'mi')) from ta_zeitreihe zb, ta_zeitreihe zv where zv.aufgaben_nr = 2 and zv.probant = z.probant and zb.probant = zv.probant and zv.aufgaben_nr = zb.aufgaben_nr-1);

  18. Tuning explain plan set statement_id='worzyk' for select((to_char(zb.datum,'ddd')*24 +to_char(zb.datum,'hh24'))*60 +to_char(zb.datum,'mi')) -((to_char(zv.datum,'ddd')*24 +to_char(zv.datum,'hh24'))*60 +to_char(zv.datum,'mi')) from ta_zeitreihe zb, ta_zeitreihe zv where zv.aufgaben_nr = 2 and zb.probant = zv.probant and zv.aufgaben_nr = zb.aufgaben_nr-1;

  19. Abfrage aus plan_table select id || LPAD(' ',2*LEVEL) || operation || ' ' || options || ' ' || object_name "Query Plan" from plan_table where statement_id = 'worzyk' connect by prior id = parent_id and statement_id = 'worzyk' start with id = 1;

  20. Tuning Query Plan -------------------------------------- 1 MERGE JOIN 2 SORT JOIN 3 TABLE ACCESS FULL TA_ZEITREIHE 4 SORT JOIN 5 TABLE ACCESS FULL TA_ZEITREIHE

  21. Tuning create index ix_teilnehmer_probant on ta_teilnehmer(probant); Query Plan --------------------------------------------- 1 NESTED LOOPS 2 TABLE ACCESS FULL TA_ZEITREIHE 3 TABLE ACCESS BY INDEX ROWID TA_ZEITREIHE 4 INDEX RANGE SCAN IX_ZEITREIHE_PROBANT

More Related