Ein kurze Geschichte der (Oracle) Zeit
Werbung
Eine kurze Geschichte der Zeit DOAG Konferenz 16. – 18.11.2010, Nürnberg Martin Hoermann [email protected] www.ordix.de Eine kurze Geschichte der (Oracle) Zeit Gaia stiftete schließlich Kronos an, den Vater mit einer Sichel zu entmannen, und Kronos wurde damit zum Herrscher der Welt und Begründer des Goldenen Zeitalters. http://www.wikipedia.de Eine kurze Geschichte der (Oracle) Zeit, Martin Hoermann 2 / 51 Eine kurze Geschichte der (Oracle) Zeit persian.sql Zusätzliche Unterlagen FAQ: 340512.1 Timestamp Ohne geht’s leider nicht… Literatur http://www.interessante.verweise/ http://www.amazon.de Infos zum Nachschlagen Wo ist Larry? Eine kurze Geschichte der (Oracle) Zeit, Martin Hoermann 3 / 51 Agenda Datentypen Zeitarithmetik Zeitzonen Kalender Sonderbares Fazit Eine kurze Geschichte der (Oracle) Zeit, Martin Hoermann 4 / 51 „Temporäre“ Datentypen in Oracle zeiten.sql DATE TIMESTAMP / TIMESTAMP WITH [LOCAL] TIME ZONE INTERVAL { YEAR TO MONTH | DAY TO SECOND } CREATE TABLE zeiten ( dt DATE , ts TIMESTAMP(9) , ts_tz TIMESTAMP WITH TIME ZONE , ts_ltz TIMESTAMP WITH LOCAL TIME ZONE , i_y2m INTERVAL YEAR(3) TO MONTH , i_d2s INTERVAL DAY(4) TO SECOND(2) ); FAQ: 340512.1 Timestamp & time zones Eine kurze Geschichte der (Oracle) Zeit, Martin Hoermann FAQ: 227334.1 Dates & Calendars 5 / 51 „Temporäre“ Datentypen in Oracle DATE / TIMESTAMP Ohne Zeitzone (frei interpretierbar) TIMESTAMP WITH TIME ZONE Wie TIMESTAMP, zuzüglich eines Zeitzonen-Offsets TIMESTAMP WITH LOCAL TIME ZONE Wie TIMESTAMP, aber Darstellung ist abhängig von Session Eine kurze Geschichte der (Oracle) Zeit, Martin Hoermann Kapitel 10 - 71 Seiten 6 / 51 Einflussfaktoren DATE / TIMESTAMP 23.11.2010 14:30:00 DATE / TIMESTAMP Session Time Zone +02:00 23.11.2010 14:30:00 DB Time Zone +00:00 Session Time Zone +06:00 23.11.2010 14:30:00 Beim SQL*Developer wird beim Timestamp in einigen Versionen eine Konvertierung vor der Anzeige vorgenommen! Eine kurze Geschichte der (Oracle) Zeit, Martin Hoermann 7 / 51 TIMESTAMP WITH TIME ZONE type_ts_tz.sql 23.11.2010 14:30:00 +02:00 TIMESTAMP WITH TIME ZONE Session Time Zone +02:00 23.11.2010 12:30:00 +02:00 DB Time Zone +00:00 UTC Session Time Zone +06:00 23.11.2010 14:30:00 +02:00 Eine kurze Geschichte der (Oracle) Zeit, Martin Hoermann Bei Konvertierungen kann die Session Time Zone eine Bedeutung haben! 8 / 51 TIMESTAMP WITH TIME ZONE type_ts_tz.sql 23.11.2010 14:30:00 Europe/Berlin TIMESTAMP WITH TIME ZONE Session Time Zone +02:00 23.11.2010 13:30:00 133/252 DB Time Zone +00:00 UTC Session Time Zone +06:00 23.11.2010 14:30:00 Europe/Berlin Eine kurze Geschichte der (Oracle) Zeit, Martin Hoermann Bei Konvertierungen kann die Session Time Zone eine Bedeutung haben! 9 / 51 TIMESTAMP WITH LOCAL TIME ZONE type_ts_ltz.sql 23.11.2010 14:30:00 +03:00 Wenn TZH:TZM vorhanden dann Vorang vor Session Time Zone TIMESTAMP WITH (LOCAL) TIME ZONE Session Time Zone +02:00 Delta = DB Time Zone - NVL( TZH:TZM, Session Time Zone) DB Time Zone +00:00 23.11.2010 11:30:00 TZH:TZD Oracle recommends that you set the database time zone to UTC (0:00). Doing so can improve performance, especially across databases, as no conversion of time zones will be required. Delta = Session Time Zone - DB Time Zone Session Time Zone +06:00 23.11.2010 17:30:00 Eine kurze Geschichte der (Oracle) Zeit, Martin Hoermann 10 / 51 Umwandlung: Text in Datum Literale (ANSI) DATE '2010-11-13' ohne Uhrzeiten TIMESTAMP '2010-11-13 23:59:59.123456789 +01:00' Eine kurze Geschichte der (Oracle) Zeit, Martin Hoermann div. Varianten 11 / 51 Umwandlung: Text in Datum String in NLS-Interpretation ALTER SESSION SET NLS_DATE_FORMAT = 'DD.MM.YY HH24:MI:SS'; INSERT INTO ... VALUES( '31.12.10 23:59:59',... ); Funktionen zur expliziten Umwandlung to_date( '31.12.10 23:59:59', 'DD.MM.YY HH24:MI:SS' ) to_timestamp( '31.12.2010 23:59:59.123' , 'DD.MM.YYYY HH24:MI:SS.FF3' ) Eine kurze Geschichte der (Oracle) Zeit, Martin Hoermann 12 / 51 Umwandlung: Datum in Text Funktionen zur expliziten Umwandlung to_char( dt, 'DD.MM.YYYY HH24:MI:SS' ) Format Modell Datums-Datentyp Eine kurze Geschichte der (Oracle) Zeit, Martin Hoermann 13 / 51 Umwandlung: Datum in Text 01.01.2011 00:00:01 'Day Month SYYYY' => Samstag 'WW YYYY' Mit Leerzeichen gefüllt Januar 2011 => 01 2011 Kalenderwoche vs. ISO-Woche 'IW IYYY' => 52 2010 'J' => 2.455.563 'DDD' => 001 Eine kurze Geschichte der (Oracle) Zeit, Martin Hoermann Julian Day Tag des Jahres 14 / 51 Umwandlung: Datum in Text nls_calendar Definiert Kalendersystem nls_date_language Sprachspezifische Elemente abgeleitet von nls_date_format nls_timestamp_format nls_timestamp_tz_format nls_territory Format für implizite Konvertierung Internal - ohne Bedeutung nls_time_format nls_time_tz_format Eine kurze Geschichte der (Oracle) Zeit, Martin Hoermann 15 / 51 Teil vom Ganzen Ein Datum ist nicht mehr als die Summe seiner Bestandteile… Eine kurze Geschichte der (Oracle) Zeit, Martin Hoermann 16 / 51 Agenda Datentypen Zeitarithmetik Zeitzonen Kalender Sonderbares Fazit Eine kurze Geschichte der (Oracle) Zeit, Martin Hoermann 17 / 51 Zeitarithmetik sysdate = (sysdate + 1) - 1 Heute ist morgen schon gestern… Eine kurze Geschichte der (Oracle) Zeit, Martin Hoermann 18 / 51 Zeitarithmetik, die gute alte Zeit (DATE) sysdate + 1 + 1 Tag sysdate - 1/24 - 1 Stunde sysdate + to_dsinterval( '+ 7 00:00:00') sysdate - to_yminterval( '+ 1-2') + 7 Tage + 1 Jahr und 2 Monate SQL> SELECT date '2012-02-29' + to_yminterval( '+ 1-0') * FEHLER in Zeile 1: ORA-01839: Datum für angegebenen Monat nicht gültig 29. Feb. 2013 existiert nicht Eine kurze Geschichte der (Oracle) Zeit, Martin Hoermann 19 / 51 Zeitarithmetik NUMBER DATE DATE {+|-} TIMESTAMP{+|-} INTERVAL DATE INTERVAL NUMBER DATE INTERVAL TIMESTAMP TIMESTAMP INTERVAL Eine kurze Geschichte der (Oracle) Zeit, Martin Hoermann 20 / 51 Zeitarithmetik INTERVAL{+|-} INTERVAL{*|/} Eine kurze Geschichte der (Oracle) Zeit, Martin Hoermann DATE DATE INTERVAL INTERVAL TIMESTAMP INTERVAL NUMBER INTERVAL 21 / 51 Literatur Eine kurze Geschichte der (Oracle) Zeit, Martin Hoermann 22 / 51 Agenda Datentypen Zeitarithmetik Zeitzonen Kalender Sonderbares Fazit Eine kurze Geschichte der (Oracle) Zeit, Martin Hoermann Harrision H4 1753 http://www.fotos-reiseberichte.de/ 23 / 51 Zeitzonen: John Harrinson, H4 1753 Eine kurze Geschichte der (Oracle) Zeit, Martin Hoermann 24 / 51 Zeitzonen: New York, Rio, Tokio $ORACLE_HOME/oracore/zoneinfo/ ORA_TZFILE Umgebungsvariable übersteuer Default Eine kurze Geschichte der (Oracle) Zeit, Martin Hoermann 25 / 51 Zeitzonen zeitzonen.sql Von Europe/Berlin nach UTC FROM_TZ(<timestamp> ,'Europe/Berlin') AT TIME ZONE '+00:00' Von UTC nach Europe/Berlin FROM_TZ(<timestamp> , '+00:00') AT TIME ZONE 'Europe/Berlin' Eine kurze Geschichte der (Oracle) Zeit, Martin Hoermann 26 / 51 Zeitzonen: Daylight Savings error_on_overlap_time.sql UTC ------------------31.10.2010 00:59:00 31.10.2010 01:59:00 UTC2BERLIN ----------------------------31.10.2010 02:59:00 +02:00 31.10.2010 02:59:00 +01:00 ALTER SESSION SET error_on_overlap_time=true; FEHLER in Zeile 2: ORA-01883: Überlappung wurde während einem Regionsübergang deaktiviert If you do not add the TZD format element, and the datetime value is ambiguous, then Oracle Database returns an error if you have the ERROR_ON_OVERLAP_TIME session parameter set to TRUE. If ERROR_ON_OVERLAP_TIME is set to FALSE (the default value), then Oracle Database interprets the ambiguous datetime as Standard Time. Eine kurze Geschichte der (Oracle) Zeit, Martin Hoermann 27 / 51 Zeitzonen timezone.sql Umrechnung von UTC nach Lokal bei nicht eindeutigen Zeiten FUNCTION daylight_switch_order ... UTC ------------------30.10.2010 23:00:00 UTC2BERLIN ORDER --------------------------- -----31.10.2010 01.00:00 +02:00 0 31.10.2010 00:00:00 31.10.2010 02.00:00 +02:00 1 31.10.2010 01:00:00 31.10.2010 02.00:00 +01:00 2 31.10.2010 02:00:00 31.10.2010 03.00:00 +01:00 0 Eine kurze Geschichte der (Oracle) Zeit, Martin Hoermann 28 / 51 Zeitzonen Client SYSDATE, TIMESTAMP, ... SYSTIMESTAMP,TZ_OFFSET, CURRENT_DATE, SESSIONTIMEZONE... Server Prozess ORA_SDTZ TZ Listener Betriebssystem Uhr Eine kurze Geschichte der (Oracle) Zeit, Martin Hoermann 29 / 51 DBMS-Scheduler BEGIN DBMS_SCHEDULER.set_scheduler_attribute( attribute => 'default_timezone', value => 'Europe/Berlin'); END; / Repeating jobs with frequencies smaller than daily follow their frequencies exactly across daylight savings adjustments. For example, suppose that a job is scheduled to repeat every 3 hours, the clock is moved forward from 1:00 a.m. to 2:00 a.m., and the last time the job ran was midnight. Its next scheduled time will be 4:00 a.m. Thus, the 3 hour period between subsequent job runs is retained. The same applies when the clock is moved back. This behavior is not the case for repeating jobs that have frequencies of daily or larger. For example, if a repeating job is supposed to be executed on a daily basis at midnight, it will continue to run at midnight if the clock is moved forward or backward. When the execution time of such a daily (or larger frequency) job happens to fall inside a window where the clock is moved forward, the job executes at the end of the window. Oracle® Database PL/SQL Packages and Types Reference - 114 DBMS_SCHEDULER Eine kurze Geschichte der (Oracle) Zeit, Martin Hoermann 30 / 51 DBMS-Scheduler DBMS_SCHEDULER.CREATE_JOB( job_name => 'J_UPDAT1', start_date => TRUNC(SYSDATE), repeat_interval => 'FREQ = DAILY;BYHOUR=15;BYMINUTE=0;BYSECOND=0;INTERVAL = 1', ... DBMS_SCHEDULER.CREATE_JOB( job_name => 'J_UPDAT2', start_date => to_timestamp_tz('2010-11-03 Europe/Zurich', 'YYYY-MM-DD TZR'), repeat_interval => 'FREQ = DAILY;BYHOUR=15;BYMINUTE=0;BYSECOND=0;INTERVAL = 1', ... Eine kurze Geschichte der (Oracle) Zeit, Martin Hoermann 31 / 51 Upgrade Time Zone File USA 2006 USA 2007 23.11.2010 13:30:00 133/252 UTC TZR ID 357056.1 Impact of changes to daylight saving time (DST) rules on the Oracle database ORA_DST_AFFECTED Enables you to verify whether the data in a column is affected by upgrading the DST rules from one version to another version ORA_DST_CONVERT Enables you to upgrade your TSTZ column data from one version to another ORA_DST_ERROR Enables you to verify that there are no errors when upgrading a datetime value Eine kurze Geschichte der (Oracle) Zeit, Martin Hoermann 32 / 51 Agenda Datentypen Zeitarithmetik Zeitzonen Kalender Sonderbares Fazit http://www.muenster.de/ Eine kurze Geschichte der (Oracle) Zeit, Martin Hoermann 33 / 51 Kalender Wie viele Tage hat die Woche? Wie viele Tage hat welcher Monat? Wie viele Tage hat ein Jahr? http://www.ortelius.de/kalender/ Eine kurze Geschichte der (Oracle) Zeit, Martin Hoermann 34 / 51 Kalender: Wochentage next_day.sql Tag der Woche bestimmen… SELECT NEXT_DAY('02-FEB-2001','TUESDAY') "NEXT DAY" FROM DUAL; NEXT DAY ----------06-FEB-2001 Eine kurze Geschichte der (Oracle) Zeit, Martin Hoermann 35 / 51 Kalender: Monatsrechnung monate.sql Welches ist der letzte Tag im Monat? last_day( datum ) Wie viele Tage hat welcher Monat? MM -01 02 03 04 05 06 07 08 09 10 11 12 MONAT ANZAHL_TAGE --------------- ----------Farwarden 31 Aordebhsht 31 Khordad 31 Te 31 Amordad 31 Shahruoar 31 Mehr 30 Aban 30 Athar 30 Dei 30 Bahman 30 Esfand 29 Persischer Kalender Eine kurze Geschichte der (Oracle) Zeit, Martin Hoermann 36 / 51 Kalender: Jahresrechnung persian.sql Welches ist der erste Tag im Jahr? to_char( , , ) tag 'Month, DD.MM.YYYY' 'NLS_CALENDAR=''PERSIAN''' AS tag_persian SQL> @persian TAG_GREGOR ---------19.03.2011 20.03.2011 21.03.2011 22.03.2011 TAG_PERSIAN -----------------------------Esfand , 28.12.1389 Esfand , 29.12.1389 Farwarden , 01.01.1390 Farwarden , 02.01.1390 Eine kurze Geschichte der (Oracle) Zeit, Martin Hoermann 37 / 51 Kalender Wie viele Tage hat ein Jahr? Definition Schaltjahre / Schaltsekunden Kalender Tage Jahr 0 Jahr 2000 365,24244 365,24218 ∆ 22,464 Sekunden Julianisch 365,25 ∆ 653 Sekunden Persisch 365,24220 ∆ 20 Sekunden Gregorianisch 365,2425 Eine kurze Geschichte der (Oracle) Zeit, Martin Hoermann zum Jahr 0 ∆ 5 Sekunden 38 / 51 Kalender: gregorianische Kalender gregor.sql to_char( DATE '1582-10-01' + level, 'DD.MM.YYYY') DATUM -------------01.10.1582 02.10.1582 03.10.1582 04.10.1582 15.10.1582 16.10.1582 http://www.wikipedia.de - Papst Gregor X Eine kurze Geschichte der (Oracle) Zeit, Martin Hoermann 39 / 51 Kalender: gregorianische Kalender Eine kurze Geschichte der (Oracle) Zeit, Martin Hoermann 40 / 51 Kalender: Schaltjahre CASE WHEN( mod( extract( Year FROM tag), 400) = 0 ) THEN 'Leap' WHEN( mod( extract( Year FROM tag), 100) = 0 ) THEN 'No Leap' WHEN( mod( extract( Year FROM tag), 4) = 0 ) THEN 'Leap' ELSE 'No Leap' END DATUM LEAP_YEAR ----------- ----------01.03.1900 No Leap 29.02.2000 Leap 01.03.2001 No Leap 29.02.2004 Leap Eine kurze Geschichte der (Oracle) Zeit, Martin Hoermann 41 / 51 Agenda Datentypen Zeitarithmetik Zeitzonen Kalender Sonderbares Fazit Eine kurze Geschichte der (Oracle) Zeit, Martin Hoermann 42 / 51 Jahr 0 / Schaltjahre Bug ad_bc.sql ASTRO COMMON ---------- ------4 4 A.D. 3 3 A.D. 2 2 A.D. 1 1 A.D. 0 1 B.C. -1 2 B.C. -2 3 B.C. -3 4 B.C. -4 5 B.C. -5 6 B.C. Eine kurze Geschichte der (Oracle) Zeit, Martin Hoermann ORACLE -------4 A.D. 3 A.D. 2 A.D. 1 A.D. 1 B.C. 2 B.C. 3 B.C. 4 B.C. 5 B.C. 6 B.C. Schaltjahre 43 / 51 Jahr 0 Bug jahr0.sql SQL> SELECT date '0001-01-01' -1 FROM dual; DATUM -----------31.12.0000 SQL> SELECT to_date( '31.12.0000', 'DD.MM.YYYY' ) FROM dual ORA-01841: (Volles) Jahr muss zwischen -4713 und +9999 liegen und darf nicht 0 sein http://www.ocelot.ca/ocelot.htm Eine kurze Geschichte der (Oracle) Zeit, Martin Hoermann 44 / 51 Internes Format DUMP( TO_DATE('02.04.1804 15:30:10', 'DD.MM.YYYY HH24:MI:SS' )) --------------------------------------------------------------Typ=13 Len=8: 7,12, 4, 2,15,30,10, 0 | | | | | | | | | | | | | | | | | | | |-- immer 0 | | | | | | | | |----- Sekunden | | | | | | | |-------- Minuten | | | | | | |----------- Stunden | | | | | |-------------- Tag | | | | |----------------- Monat | | |-|-------------------- Jahr als Tupel | |-------------------------- Länge in Byte |-------------------------------- Datentyp Wenn das gleiche Datum aus der Datenbank gelesen wird, werden einige Spalten mit einem Offset angezeigt. Eine kurze Geschichte der (Oracle) Zeit, Martin Hoermann 45 / 51 Agenda Datentypen Zeitarithmetik Zeitzonen Kalender Sonderbares Fazit Eine kurze Geschichte der (Oracle) Zeit, Martin Hoermann http://www.uhrenstadt-glashuette.de 46 / 51 Weitere Vorträge Vorträge zum Thema Oracle Vorträge zum Thema Java Vortrag zum Thema MySQL Vorträge zum Thema IT-Management DOAG Schulungstag Eine kurze Geschichte der (Oracle) Zeit, Martin Hoermann 47 / 51 Begleitende CD ... gibt es am ORDIX Stand, Ebene 3, Stand 324 Eine kurze Geschichte der (Oracle) Zeit, Martin Hoermann 48 / 51 Welche Zeit ist die Richtige? www.union-glashuette.com www.muehle-glashuette.de www.glashuette.com www.chronometerwerkeglashuette.de www.glashuette-original.com www.alange-soehne.com www.soehnle-uhren.de www.tutima.de Eine kurze Geschichte der (Oracle) Zeit, Martin Hoermann 49 / 51 Wie programmiere ich Kalender? Eine kurze Geschichte der (Oracle) Zeit, Martin Hoermann 50 / 51 Grande Complication Grande Complication ist die französische Bezeichnung für ein komplexes Werk einer Armband- oder Taschenuhr, welches neben dem normalen Gehwerk (Stunde, Minute und Sekunde) noch weitere Funktionen (Komplikationen, frz. Complication) aufweist. http://www.wikipedia.de Eine kurze Geschichte der (Oracle) Zeit, Martin Hoermann 51 / 51 Vielen Dank für Ihre Zeit! einfach. gut. beraten. Quellenangabe Folie 2: http://www.wikipedia.de Folie 3: http://www.amazon.de Folie 4: http://www.sxc.hu Folie 17: http://www.sxc.hu Folie 22: http://www.fotos-reiseberichte.de/ Folie 24: http://www.sxc.hu Folie 30: http://www.muenster.de/ Folie 31: http://www.ortelius.de/kalender/ Folie 40: http://www.ocelot.ca/ocelot.htm Folie 42: http://www.uhrenstadt-glashuette.de Folie 43: www.union-glashuette.com www.muehle-glashuette.de www.glashuette.com www.chronometerwerke-glashuette.de www.glashuette-original.com www.alange-soehne.co www.soehnle-uhren.de www.tutima.de Folie: 44: http://www.wikipedia.de Eine kurze Geschichte der (Oracle) Zeit, Martin Hoermann
