Could the Local Session Time Zone be the cause?
BTW-Is Getdate() new to 10g. I don't see it in the 9i docs. But it is a known SQL Server function. Or do you mean sysdate?
CURRENT_TIMESTAMP returns the current date and time in the session time zone, in a value of datatype TIMESTAMP WITH TIME ZONE. The time zone displacement reflects the current local time of the SQL session. If you omit precision, then the default is 6. The difference between this function and LOCALTIMESTAMP is that CURRENT_TIMESTAMP returns a TIMESTAMP WITH TIME ZONE value while LOCALTIMESTAMP returns a TIMESTAMP value.
In the optional argument, precision specifies the fractional second precision of the time value returned.
Examples
The following example illustrates that CURRENT_TIMESTAMP is sensitive to the session time zone:
ALTER SESSION SET TIME_ZONE = '-5:0';
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL;
SESSIONTIMEZONE CURRENT_TIMESTAMP
--------------- ---------------------------------------------------
-05:00 04-APR-00 01.17.56.917550 PM -05:00
ALTER SESSION SET TIME_ZONE = '-8:0';
SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL;
SESSIONTIMEZONE CURRENT_TIMESTAMP
--------------- ----------------------------------------------------
-08:00 04-APR-00 10.18.21.366065 AM -08:00
If you use the CURRENT_TIMESTAMP with a format mask, take care that the format mask matches the value returned by the function. For example, consider the following table:
CREATE TABLE current_test (col1 TIMESTAMP WITH TIME ZONE);
The following statement fails because the mask does not include the TIME ZONE portion of the type returned by the function:
INSERT INTO current_test VALUES
(TO_TIMESTAMP_TZ(CURRENT_TIMESTAMP, 'DD-MON-RR HH.MI.SSXFF PM'));
The following statement uses the correct format mask to match the return type of CURRENT_TIMESTAMP:
INSERT INTO current_test VALUES (TO_TIMESTAMP_TZ
(CURRENT_TIMESTAMP, 'DD-MON-RR HH.MI.SSXFF PM TZH:TZM'));
-----Original Message-----
From: Calvin Shafer
[mailto:oracledba-ezmlmshield-x75083139.[Email address protected]
Sent: Monday, January 29, 2007 1:11 PM
To: LazyDBA Discussion
Subject: RE: CURRENT_TIMESTAMP vs GETDATE
Correction: Maybe the timestamp is not affected by DST changes *so* they are
accurate when determining the length of time between log entries?
-----Original Message-----
From: Calvin Shafer [mailto:[Email address protected]
Sent: Monday, January 29, 2007 4:10 PM
To: 'LazyDBA Discussion'
Subject: RE: CURRENT_TIMESTAMP vs GETDATE
Maybe the timestamp is not affected by DST changes to they are accurate when
determining the length of time between log entries?
-----Original Message-----
From: Taylor Jay
[mailto:oracledba-ezmlmshield-x40123612.[Email address protected]
Sent: Monday, January 29, 2007 3:18 PM
To: LazyDBA Discussion
Subject: RE: CURRENT_TIMESTAMP vs GETDATE
Chris,
I agree that they should have returned the same value. We updated the Unix
box with a patch for the new date for DST. We then did a test via a qry tool
and our applications to make sure everything was working. It was. We then
moved the date on the server to 11-Mar-07 at 01:30. At 02:00 the server
properly jumped to 03:00, however at this point the CURRENT_TIMESTAMP and
the GETDATE returned different values. Here in
lies my interest. As stated below "Returns the current date and time.
This function is equivalent to GETDATE.", what makes them equivalent and not
identical? The GETDATE reflected the server time change the
CURRENT_TIMESTAMP did not. Any ideas?
Thanks again.
Cheers-
Jay
-----Original Message-----
From: Chris
[mailto:oracledba-ezmlmshield-x47222144.[Email address protected]
Sent: Monday, January 29, 2007 14:28
To: LazyDBA Discussion
Subject: RE: CURRENT_TIMESTAMP vs GETDATE
From BOL (2005).
"Returns the current date and time. This function is equivalent to GETDATE."
-----Original Message-----
From: Taylor Jay
[mailto:oracledba-ezmlmshield-x31449968.[Email address protected]
Sent: Monday, January 29, 2007 9:35 AM
To: LazyDBA Discussion
Subject: CURRENT_TIMESTAMP vs GETDATE
Can anyone provide some insight into why these two commands would return
different values. We did some testing of the patches on the new day light
savings time schedule this weekend. During the testing it was brought to
light that these two commands were returning different values. Formatting
aside, they were different. Should these not return that same data and time?
Thanks in advance for any explanation.
Cheers-
Jay
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a dba
job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com To
unsubscribe: http://www.lazydba.com/unsubscribe.html
--
This message has been scanned for viruses and dangerous content by
MailScanner, and is believed to be clean.
Douglas County, Oregon
www.co.douglas.or.us
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a dba
job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com To
unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a dba
job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com To
unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
--
This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean.
Douglas County, Oregon
www.co.douglas.or.us
Oracle LazyDBA home page