SQL/Systemzeit per Stored Procedure auslesen

[robroy85]

Hallo Community!

Ich möchte gerne über einen T-SQL Befehl die System- bzw. SQL-Zeit als Timestamp auslesen! und zwar wenigstens sekundengenau.

In diesem DP-post hatte ich von meiner Warenwirtschaft geschrieben, die Timestamps als Primarykeys benutzt, allerdings werden diese von der Warenwirtschaft erstellt und nicht vom SQLServer und auch als varchar gespeichertund sind demzufolge keine "echten" timestamps.

Nun muss ich aus einer stored procedure heraus selber eine solche ID erstellen, um einen INSERT-Befehl auszuführen


Schönen Gruß Robert

[Hansa]

Hi,

ich verstehe zwar nur Bahnhof, aber es gibt NOW und CURRENT_TIMESTAMP. Das kann man verwenden.

[Jelly]

SQL-Code:

select getdate()

sollte das sein, was Du suchst.

[marabu]

Ich würde die Funktion GetUTCDate() verwenden...

Grüße vom marabu

[robroy85]

Danke erstmal für die raschen Antworten, wobei ich mit NOW und GetUTCDate() nichts anfangen konnte da sie in meinem Query Analyser Fehler verursachten.

Um GetDate für mein Beispiel zu benutzen:

SQL-Code:

select getdate()

ERGEBNIS:

                            
--------------------------- 
2005-10-19 14:33:56.297

(1 row(s) affected)

Das ist schon das Ergebnis, was ich an sich haben möchte, doch kann ich so damit nichts anfangen.

Das Datetime-Format soll Sekundengenau in einen UNIX-Timestamp umgewandelt werden, der dann in eine Hexadezimalzhal umrechnet wird.

Als Delphi-Anweisung sieht das so aus:

Delphi-Quellcode:

 
edit1.Text := IntToHex(DateTimeToUnix(Now),8);

schönen gruß, robert

[Jelly]

Na dann bist du ja fast am Ziel... Nimm ne TQuery mit meinem SQL Statement:

SQL-Code:

select getdate() as ServerZeit

Und überweise deinem QT das DBFeld:

Delphi-Quellcode:

edit1.Text := IntToHex(DateTimeToUnix(query1.fieldbyname('ServerZeit').AsDateTime),8);

[robroy85]

nope.. wie mans macht isses verkehrt ;-)

der delphi-quelltext sollte als Beispiel dienen ( wie ich es in delphi machen würde )

Ich muss aber den hex-Wert innerhalb einer Stored Procedure (sp) erhalten, da er dort dann direkt als Primary Key eines Datensatzes eingefügt wird (über INSERT).

Leider kann ich den HEX-wert auch nicht aus delphi heraus an die sp übergeben, auch wenn ich diese von dort auffrufe, da ich nicht weiß wieviele Primary-Keys gebraucht werden (Schleifenfunktion)

nochmals schönen gruß

[Jelly]

Wieso nutzt du auf einem MSSQL Server einen Unix Timestamp als Primary Key :gruebel: Das geht mir nicht in den Kopf.

Dann musst du Dir halt ne Funktion in MSSQL basteln die dir einen datetime Typ in einen Unixtimestamp umwandelt. Meines Wissens bietet der MSSQL von Haus keine solche Funktion. Wenn Du mir also den Algorithmus sagen kannst, wie so eine Unix Timestampt aufgebaut ist, kann ich Dir vielleicht helfen.

[marabu]

Hallo Robert,

Zitat:

Zitat von robroy85

wobei ich mit NOW und GetUTCDate() nichts anfangen konnte da sie in meinem Query Analyser Fehler verursachten.

tut mir leid, aber ich hatte übersehen, dass du MSSQL 7 verwendest. Da hilft dir vielleicht das hier:

SQL-Code:

CREATE PROCEDURE GetUTCDate AS
BEGIN
  DECLARE @BiasInMinutes INT
  EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 
    'SYSTEM\CurrentControlSet\Control\TimeZoneInformation', 
    'ActiveTimeBias', 
    @BiasInMinutes OUTPUT
  SELECT DATEADD(MINUTE, @BiasInMinutes, GetDate())
END

Vom Ergebnis musst du dann den gebrochenen Anteil nehmen, mit der Zahl der Sekunden im Tag (86.400) multiplizieren und dieses Ergebnis dann in eine INTEGER konvertieren. Jetzt brauchst du nur noch eine SP mit der Funktionalität von IntToHex(int, places) und dann hast du es geschafft.

Grüße vom marabu

[robroy85]

Leider muss ich mich an die Vorgaben halten, die mir die installierte Warenwirtschaft vorsetzt. Und diese nutzt leider keinen "echten" Timestamp sondern eine 10stellige varchar als Primary Key.
Die 10 Stellen bestehen aus 2 aneinandergesetzten Gruppen . Die erste Gruppe ist eine 8-stellige HEX-Zahl. Der Rest ist eine 2-stellige HEX-Zahl.
Die ersten 8 Ziffern sind eine Hexzahl die, umgewandelt in Integer, die Anzahl der Sekunden seit dem 01.01.1970 00:00:00 GMT ergeben. Also der klassische UNIX-Timestamp. Die letzten 2 Ziffern sind Indikatoren für die tausendsel Sekunden. Dass heißt nach meine Vermutungen, dass die letzten 2 Zeichen, konvertiert zu Integer, mit knapp 4 multipliziert die tausendsel ergeben.
Ich versuche mal dieses Verworrene an einem Beispiel zu erläutern:

In der Warenwirtschaft wird eine Rechnung geschrieben und abgespeichert. Der neue Eintrag in der Rechnungstabelle bekommt dann als Primary Key den Wert '4354a0520f'

$4354a052 ergibt als Integerzahl 1129619538. Es sind also 1129619538 Sekunden seit dem 01.01.1970 00:00:00 GMT vergangen. Das passt, ich habs schon bei Beispielen nachgerechnet.

$0f ergibt 15 in der ascii-tabelle 15. 15 * 1000/256 = 58.59...=> 0.059

Also wurde die Rechnung 1129619538,059 Sekunden nach 01.01.1970 00:00:00 GMT geschrieben.

Hilft das Euch vielleicht weiter?
Die logik des ganzen habe ich mittlerweile soweit durchschaut, jedoch fehlt mir "noch" das Basiswissen an Befehlen. Ich bin immer fleißig in der MSDN Library am lesen :nerd:

MfG robert

[robroy85]

@marabu

ich habe die Funktion GetUTCDate dann auch in der MSDN unter MS SQL 2000 gefunden und mir ist der fehler klar geworden.
Ich habe sie als procedure hinzugefügt, jedoch ist sie doch vom Output gleich wie getdate "nur" das die Zeitzone und Winter/Sommerzeit berücksichtigt wird.

Leider habe ich das mit dem gebrochenen anteil * 60*60*24 nicht verstanden!
Villeicht hilft ja auch die Erläuterung in meinem vorletzten Post weiter?

Wie konvertiere ich den das datetime-format in einen integerwert?
ich hatte schon mit

SQL-Code:

Convert([..],getdate())

versucht, aber da ist nix gescheites bei raus gekommen

schönen gruß robert

[marabu]

Hallo Robert,

Zitat:

Zitat von robroy85

Ich habe sie als procedure hinzugefügt, jedoch ist sie doch vom Output gleich wie getdate "nur" das die Zeitzone und Winter/Sommerzeit berücksichtigt wird.

Ist doch genau die Zeit, die du brauchst. Werfe den ersten Entwurf weg und nimm die hier - die hat einen Ausgabeparameter:

SQL-Code:

IF EXISTS (
  SELECT name FROM sysobjects 
    WHERE name = 'GetUTCDate' AND type = 'P'
) DROP PROCEDURE GetUTCDate

CREATE PROCEDURE GetUTCDate
  @utc DATETIME OUTPUT
AS 
BEGIN
  DECLARE @BiasInMinutes INT
  EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 
    'SYSTEM\CurrentControlSet\Control\TimeZoneInformation', 
    'ActiveTimeBias', 
    @BiasInMinutes OUTPUT
  SELECT @utc = DATEADD(MINUTE, @BiasInMinutes, GetDate())
END

Zitat:

Zitat von robroy85

Leider habe ich das mit dem gebrochenen anteil * 60*60*24 nicht verstanden!

Vergiss den gebrochenen Anteil - Denkfehler von mir - war schon beim Einkaufen...

Zitat:

Zitat von robroy85

Wie konvertiere ich den das datetime-format in einen integerwert?

Vergiss das auch wieder, du hast in MSSQL 7 noch keinen Datentyp BigInt. So hast du schon mal die UTC-Zeit in Sekunden:

SQL-Code:

DECLARE @utc DATETIME
DECLARE @seconds FLOAT
EXEC getutcdate @utc OUTPUT
SELECT @seconds = CONVERT(FLOAT, @utc) * 86400

Jetzt fehlt nur noch die Schleife, die aus dem Float-Wert einen Hex-String macht, aber das kriegen wir auch noch hin.

marabu

[robroy85]

Ich habe nun eine recht simple UNIX_timestam-stored procedure erstellt. Mit dem Ergebnis von CONVERT(FLOAT, @utc) * 86400 konnte ich leider nichts anfangen. die SP erzeugt nun das gleiche Ergebnis wie die delphi-funktion IntToStr(DateTimeToUnix(Now)) :dancer2:

SQL-Code:

CREATE PROCEDURE sp_timestamp 

AS
  DECLARE @jahr int
  DECLARE @sec int 
  SELECT @jahr = 1970
  SELECT @sec = 0

WHILE (CONVERT (int, DATEPART (yyyy, getdate())) > @jahr)
BEGIN
   if ( @jahr % 4 ) = 0 AND (@jahr % 100)<>0 OR (@jahr % 1000) = 0  
      SELECT @sec = @sec + 366*24*60*60
   ELSE SELECT @sec = @sec + 365*24*60*60 
      SELECT @jahr = @jahr +1
END

SELECT @sec = @sec + (CONVERT (int, DATEPART (dy, getdate()))-1)*24*60*60
SELECT @sec = @sec + CONVERT (int, DATEPART (hh, getdate()))*60*60
SELECT @sec = @sec + CONVERT (int, DATEPART (mi, getdate()))*60
SELECT @sec = @sec + CONVERT (int, DATEPART (ss, getdate()))

PRINT  @sec  
PRINT 'Sekunden sind vergangen seit dem 01.01.1970 00:00:00' 
SELECT getdate()

ERGEBNIS:
1129761963
Sekunden sind vergangen seit dem 01.01.1970 00:00:00

---------------------------
2005-10-19 22:46:03.187

(1 row(s) affected)

Jetzt muss nur noch das ganze in eine HEX-zahl umgewandelt werden. Gibt es dafür wirklich keine T-SQL Funktion ?

Schönen Gruß

Robert

[Jelly]

Zitat:

Zitat von robroy85

Jetzt muss nur noch das ganze in eine HEX-zahl umgewandelt werden. Gibt es dafür wirklich keine T-SQL Funktion ?

Nicht dass ich wüsste. Hier aber ne schnelle Lösung in Delphi um dezimal in hexadezimal umzuwandeln. Sollte dann nicht so schwer sein, das in T-SQL umzucoden:

Delphi-Quellcode:

const
 Trans : array[0..15] of char = ('0','1','2','3','4','5','6','7','8','9',
                                 'A','B','C','D','E','F') ;

function DecToHex (ID : integer) : string ;
begin
  Result := '' ;
  while ID <> 0
  do begin
          Result := Trans[ID mod 16] + Result ;
          ID := ID div 16 ;
     end ;
  if Result = '' then Result := '0';
end ;

[marabu]

Mit einer kleinen Anpassung bei GetUtcDate wird die Zeitbasis auf den Beginn des Jahres 1900 1970 umgestellt:

SQL-Code:

CREATE PROCEDURE DateTimeToUnix
  @unixtime FLOAT OUTPUT
AS
BEGIN
  DECLARE @BiasInMinutes INT, @RefDate DATETIME
  SELECT @RefDate = CONVERT(DATETIME, '19700101')
  /* ab MSSQL 2000 gibt es dann GetUtcDate */
  EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',
    'SYSTEM\CurrentControlSet\Control\TimeZoneInformation',
    'ActiveTimeBias',
    @BiasInMinutes OUTPUT
  SELECT @unixtime = CONVERT(FLOAT, DATEADD(MINUTE, @BiasInMinutes, GetDate()) - @RefDate)
END

Für die Konvertierung in einen Hex-String muss das hier herhalten:

SQL-Code:

/* ab MSSQL 2000 gibt es 64-bit integers */ 
CREATE PROCEDURE IntToHex
  @int INT,
  @len INT,
  @hex VARCHAR(8) OUTPUT AS
BEGIN
  DECLARE @i INT, @q INT, @nibble INT

  IF @len > 8 SELECT @len = 8
  SELECT @hex = ''
  WHILE (@int > 0) or (len(@hex) < @len)
  BEGIN
    SELECT @nibble = @int % 16
    SELECT @int = @int / 16
    SELECT @hex = substring('0123456789abcdef', @nibble + 1, 1) + @hex
  END
END

Und überprüfen kann man es so:

SQL-Code:

DECLARE @f FLOAT, @sec INT, @frac INT, @hs VARCHAR(8), @hf VARCHAR(2)
EXEC DateTimeToUnix @f OUTPUT
SELECT @sec = FLOOR(@f * 86400)
SELECT @frac = Floor((@f * 86400 - @sec) * 256)
EXEC IntToHex @sec, 8, @hs OUTPUT
EXEC IntToHex @frac, 2, @hf OUTPUT
SELECT @hs + @hf

Grüße vom marabu

[robroy85]

Danke für die Programmierungen!
Ich hab die Zeilen nachvollzogen und mir ist noch was aufgefallen. Soweit ich das verstehe wird die Zeitbasis nicht auf den Beginn des Jahres 1900 gerechnet, sondern wird, wie unten zu sehen, von der aktuellen Zeit, beginnend im Jahr 0, die vergangene Zeit seit dem UNIX-Zeitstart 1970 abgezogen!

[quote="marabu"]Mit einer kleinen Anpassung bei GetUtcDate wird die Zeitbasis auf den Beginn des Jahres 1900 umgestellt:

SQL-Code:

...
  SELECT @RefDate = CONVERT(DATETIME, '19700101')
...
  SELECT @unixtime = CONVERT(FLOAT, DATEADD(MINUTE, @BiasInMinutes, GetDate()) - @RefDate)
...

Ist das soweit korrekt?

Spricht denn ansonsten was gegen meine Errechnung der UNIX-Zeit?


Schönen Gruß und danke nochmal!

Robert

[marabu]

Hallo Robert,

dein Ansatz ist schon okay, ich wollte meinen Kram nur für spätere Leser in eine Form bringen, die eine sinnvolle Verwertung ermöglicht. Die 1900 war natürlich ein Schreibfehler - habe ihn schon korrigiert.

Grüße vom marabu