Datumsfunktionen von Oracle

Datenbanksysteme 3 Sommer 2001Auswertung - 1Worzyk

FH Anhalt

Datumsfunktionen von Oracle

ADD_MONTHSSyntax 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.


FH Anhalt


LAST_DAYSyntax 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.


FH Anhalt


NEXT_DAYSyntax 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.


FH Anhalt


ROUNDSyntax 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.


FH Anhalt

Datumsformate in Oracle

YYYY 4-stellige J ahreszahlQ QuartalMM Monat 01-12MONTH MonatsnameMON Abkürzung des MonatsnamensWW WochennrIW Wochennr nach ISOW Woche im MonatDDD Tag im J ahr 1-366DD Tag im Monat 1-31D Tag in der WocheDAY TagesnameDY Abkürzung des TagesnamensJ J ulianischer Tag; Tag 1 ist 1. J anuar 4712 vor Chr.HH Tagesstunde 1-12HH24 Tagesstunde 1-24MI MinuteSS Sekunde


FH Anhalt

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


FH Anhalt


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


FH Anhalt


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


FH Anhalt


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


FH Anhalt


FH Anhalt

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.


FH Anhalt


FH Anhalt

Auswertung pro Stunde

select to_char(datum,'hh24'), count(*)from ta_zeitreihegroup by to_char(datum,'hh24')order by to_char(datum,'hh24');


FH Anhalt


FH Anhalt

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');


FH Anhalt


FH Anhalt

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');


FH Anhalt

ta_teilnehmerEin Eintrag pro Teilnehmer, für jede Antwort eine

Spalte30051980070919811 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


FH Anhalt

ta_teilnehmerTesten 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);


FH Anhalt

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


FH Anhalt

ta_zeitupdate 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);


FH Anhalt

Tuningexplain 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;


FH Anhalt

Abfrage aus plan_table

select id || LPAD(' ',2*LEVEL) || operation || ' ' || options || ' '

|| object_name "Query Plan"from plan_tablewhere statement_id = 'worzyk'connect by prior id = parent_id and

statement_id = 'worzyk'start with id = 1;


FH Anhalt

Tuning

Query Plan

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

1 MERGE JOIN

2 SORT JOIN

3 TABLE ACCESS FULL TA_ZEITREIHE

4 SORT JOIN



FH Anhalt

Tuning

create index ix_teilnehmer_probant on ta_teilnehmer(probant);

Query Plan

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

1 NESTED LOOPS


3 TABLE ACCESS BY INDEX ROWID TA_ZEITREIHE

4 INDEX RANGE SCAN IX_ZEITREIHE_PROBANT

Datumsfunktionen von Oracle

Documents

Transcript of Datumsfunktionen von Oracle