Subtrahieren Sie Stunden, von der jetzt () - Funktion

Wir haben eine Maschine läuft 24x7. Ich jeden Tag berichten die Anzahl der Stücke, die es in der Stunde produziert. In unserem Fall einen Werktag bedeutet '2015-06-16 06:00:00' , '2015-06-17 06:00:00' zum Beispiel.

Hier ist mein code:

select date_trunc('hour', t_el_eventlog.eventtime at time zone 'CET') as hours,
       count (distinct t_el_eventlog.serialnumber) as count
from t_el_eventlog
where eventtime at time zone 'CET' between '2015-06-16 06:00:00'
                                       and '2015-06-17 06:00:00'
and sourceid = '44'
group by hours
order by hours asc
  • Meine Postgres-version: "PostgreSQL 9.4.1, kompiliert mit Visual C++ build-1800, 32-bit"
  • Die Datentypen der beiden Spalten, die ich bin den Umgang mit: 
    eventtime timestamp without time zone
sourceid  integer NOT NULL
  • Zeitzone "Europa/Berlin".

Mit der obigen Abfrage bekomme ich die Informationen, die ich will, aber ich habe das Datum zu ändern jeden Tag. Ist es möglich, die now() Funktion als default-Wert für meinen Fall statt, so dass ich nicht haben, ändern Sie das Datum manuell Alltag?

Ihr Zeitrahmen nicht sinnvoll. Die letzten sechs Stunden fallen in "morgen" und würde nie gezählt werden (denn morgen, die neue Zeit ist nicht effektiv). Bitte klären Sie.
ich will wissen, am vorherigen Tag Ausgang...so etwas wie '2015-06-16 06:00:00' und '2015-06-17 06:00:00'
'2015-06-16 06:00:00' und '2015-06-17 06:00:00" ist ein Arbeitstag und ich möchte wissen, die Ausgabe des obigen Arbeitstag auf 2015-06-17 um 9 Uhr/10 Uhr
Mit einer Frage wie dieser, die Sie benötigen, um geben Sie Ihre genaue Tabellendefinition (und wie immer Ihre version von Postgres). Genaue Daten Typ ankommt. Auch, erzählen Sie uns mehr über Ihre Zeit zone. at time zone 'CET' ist wahrscheinlich nicht das, was Sie wollen.
So agieren Sie mit timestamp without time zone, die nichts von Zeitzonen. Warum fügen Sie at time zone 'CET' in deiner Abfrage? (Es ist schief in jedem Fall: entweder falsch oder sehr ineffizient.)

InformationsquelleAutor Chanti | 2015-06-17

  1. 6

    Antwort für timestamp

    Müssen Sie verstehen, die Art der Daten-Typen timestamp without time zone und timestamp with time zone (Namen können täuschen). Wenn Sie nicht, Lesen Sie dies zuerst:

    Den AT TIME ZONE Konstrukt verwandelt Ihren timestamp zu timestamptz, ist sicherlich die falsche Bewegung:

    where eventtime at time zone 'CET' between '2015-06-16 06:00:00'
                                       and '2015-06-17 06:00:00'

    Ersten, es killt die performance. Die Anwendung AT TIME ZONE zu eventtime macht den Ausdruck nicht sargable. Postgres kann nicht mit einem einfachen index auf eventtime. Aber auch ohne index, sargable Ausdrücke sind billiger. Bieten Grenzen angepasst, um die Werte in der Tabelle, so dass Sie nicht haben, um zu manipulieren in jeder Zeile.

    Sie könnte Ausgleich mit einem passenden Ausdruck als index, aber es ist wahrscheinlich nur ein Missverständnis und sowieso falsch.

    Was passiert in diesem Ausdruck?

    1. AT TIME ZONE 'CET' verwandelt die timestamp Wert eventtime zu timestamptz anfügen, Zeitzonen-offset von Ihrer aktuellen Zeitzone. Dies dauert DST (daylight saving time) berücksichtigt, so erhalten Sie einen anderen offset für den winter Zeitstempel. Grundsätzlich bekommen Sie die Antwort auf die Frage:

      Was ist die absolute Zeit (UTC-Zeitstempel), wenn die gegebene Zeit-zone sieht der gegebene timestamp?

      Wenn anzeigen das Ergebnis an den Benutzer-es wird nach lokalen timestamp für die aktuelle Zeitzone die Sitzung mit dem nach Zeitzonenoffset angehängt. (Kann oder kann nicht die gleiche sein wie im Ausdruck).

    2. Den string-literalen auf der rechten Seite haben keine Daten geben, um Sie, so dass Sie beabsichtigte Art ist abgeleitet aus der Zuordnung im Ausdruck. Da wir effektiv haben timestamptz jetzt sind die beiden Stimmen, um timestamptz werden, vorausgesetzt, die aktuelle Zeitzone der session.

      Mir der UTC-Zeitstempel für den Zeitpunkt, wenn die lokale Zeit sieht aus wie die gegebene Zeitstempel.

      Den offset variiert mit DST-Regeln.

    Lange Geschichte kurz, wenn Sie arbeiten mit der gleichen Zeit-zone überall: CET oder 'Europe/Berlin', dasselbe für den heutigen Zeitstempel, aber nicht für historische oder (eventuell) zukünftigen, können Sie einfach schneiden Sie die Reste.

    Den zweite problem mit dem Ausdruck: BETWEEN ist fast immer falsch mit timestamp Werte. Details:

    SELECT date_trunc('hour', eventtime) AS hour
     , count(DISTINCT serialnumber)  AS ct  -- sure you need distinct?
FROM   t_el_eventlog
WHERE  eventtime >= now()::date - interval '18 hours'
AND    eventtime <  now()::date + interval '6 hours'
AND    sourceid  =  44  -- don't quote the numeric literal
GROUP  BY 1
ORDER  BY 1;

    now() ist die PostgreSQL-Implementierung die SQL-standard CURRENT_TIMESTAMP. Beide zurück timestamptz (nicht timestamp!). Sie können entweder zu benutzen.

    now()::date entspricht CURRENT_DATE. Beide hängen ab von der aktuellen Einstellung der Zeitzone.

    Sollten Sie ein index der form:

    CREATE INDEX foo ON t_el_eventlog(sourceid, eventtime)

    Oder zu erlauben index-only-scans:

    CREATE INDEX foo2 ON t_el_eventlog(sourceid, eventtime, serialnumber)

    Wenn Sie arbeiten in unterschiedlichen Zeitzonen, werden die Dinge komplizierter, und Sie sollten timestamptz für alles.

    Alternative für timestamptz

    Bevor die Frage zu aktualisieren, es schien so, Zeitzonen Angelegenheit. Beim Umgang mit verschiedenen Zeitzonen, "heute" ist eine funktionale Abhängigkeit von der aktuellen Zeitzone. Menschen neigen dazu zu vergessen, dass.

    Nur die Arbeit mit der aktuellen Zeitzone-Einstellung der Sitzung, verwenden Sie die gleiche Abfrage wie oben. Wenn ausgeführt, in einer anderen Zeitzone, die Ergebnisse sind falsch in der Wirklichkeit. (Gilt für die oben genannten, als auch.)

    Garantieren ein korrektes Ergebnis für eine gegebene Zeitzone ('Europe/Berlin' in deinem Fall), unabhängig von der aktuellen Einstellung der Zeitzone der Sitzung, verwenden Sie diesen Ausdruck statt:

        ((now() AT TIME ZONE 'Europe/Berlin')::date - interval '18 hours')
            AT TIME ZONE 'Europe/Berlin'  -- 2nd time to convert back

    Bewusst sein, dass die Zeitzone konstruieren zurück timestamp für timestamptz Eingang und Umgekehrt.

    Wie bereits Eingangs erwähnt, all die blutigen details hier:

    Aber warum ist Ihre Lösung brauchen eine Stündliche übersicht mit generate_series? Generell, der Ausdruck tstz AT TIME ZONE 'tz' BETWEEN ts_lower AND ts_upper gemacht werden könnte sargable eher einfach, wie: tstz BETWEEN ts_lower AT TIME ZONE 'tz' AND ts_upper AT TIME ZONE 'tz'
    Die Stündliche übersicht wurde ein Ansatz. Ich wurde auf die aktualisierte Lösung, die bereits - wie Sie auch angedeutet.
    nützlich Kommentar ...danke
    Prüfen die überarbeitete Antwort für Ihre Frage zu aktualisieren.

    InformationsquelleAutor Erwin Brandstetter

  2. 4

    Ihre verwenden können CURRENT_DATE:

     select date_trunc('hour', t_el_eventlog.eventtime at time zone 'CET') as hours,
        count(distinct t_el_eventlog.serialnumber) as count
 from t_el_eventlog
 where eventtime at time zone 'CET' between CURRENT_DATE + interval '6 hour' and
                                            CURRENT_DATE + interval '30 hour' and
       sourceid = '44'
 group by hours
 order by hours asc;

    EDIT:

    Erwin ' s Kommentar über die Frage nicht diese Antwort. Mit between für Datum/Zeit ist eine schlechte Idee. Ich nehme an, dass dies wiederholt werden sollte, in jeder Frage, das dies tut. Aber das problem ist, dass die Datum/Zeit-Werte, die Grenzen zwischen den Tagen doppelt gezählt werden.

    Die richtige Logik ist:

     select date_trunc('hour', t_el_eventlog.eventtime at time zone 'CET') as hours,
        count(distinct t_el_eventlog.serialnumber) as count
 from t_el_eventlog
 where eventtime at time zone 'CET' >= CURRENT_DATE + interval '6 hour' and
       eventtime at time zone 'CET' < CURRENT_DATE + interval '30 hour' and
       sourceid = '44'
 group by hours
 order by hours asc;

    Beachten Sie das "<" für die zweite Grenze. Hier ist ein guter blog zu diesem Thema. Obwohl Aaron konzentriert sich auf SQL Server, die Warnungen (und einige der Lösungen), für andere Datenbanken.

    danke Gordon...war es gut zu wissen, dass die derzeitigen arbeiten Tag Ausgang.
    aber was ich will, ist Vortag Ausgang, und ich versuchte wie diese CURRENT_DATE + interval '6 Stunden' und CURRENT_DATE - interval '18 Uhr'
    aber es did ' NT Arbeit....es nicht zeigen keine Fehler, weder Ausgang
    Sie sind einschließlich der oberen Grenze, die Schaffung einer Ecke Fall 25th hour. Und Sie sind völlig ignorierte Zeitzonen und die tatsächlichen Daten-Typen. Es ist auch nicht sargable und damit sehr ineffizient. Ich bezweifle, dass Menschen, die Abstimmung für diese gewesen zu denken, es durch.
    Ich bin mir bewusst, dass. Damit soll die Umsetzung der Abfrage, die der OP gefragt. Eigentlich sollte man hinzufügen, dass der Kommentar auf die Frage.

    InformationsquelleAutor Gordon Linoff

Schreibe einen Kommentar