date fomat containing timezone data
Jasbir Kular, August 14, 2002 - 8:30 pm UTC
Thanks for the info. Since my company is using 8i and they are sloooowly migrating to 9i, I might just make my own data type that contains a date and a timezone field. Then I can use this user defined data type within my tables.
August 15, 2002 - 7:55 am UTC
Well, you might consider using this technical need as a push to migrate to 9i (you are after all doing some NEW DEVELOPMENT HERE, this isn't maintenance on an existing system, this is new development -- either a brand new app or version 2 of an existing one)
Just having the TZ stored with a date is virtually useless unless you write the thousands of lines of code needed to
o support date arithmetic, subtract 5pm EST from 7pm PST. That should be 5 hours, not 2
o support TZ conversion (and with umpteen timezones -- each with different rules -- good luck)
No point having TZ datatype column
Sajid Anwar, August 15, 2002 - 9:19 am UTC
Hi Tom,
As you mentioned two points about TZ. I dont see any reason why one would go for TimeZone datatype unles and until he/she finds the way to do arithmetic on it. It really surprised me that ORACLE hasnt given anything for it. Could you please tell us atleast two good reasons WHY SHOULD WE USE TimeZone datatype?
Regards,
Sajid Anwar
August 15, 2002 - 7:04 pm UTC
Umm, Oracle9i -- there is "TIMESTAMP WITH TIMEZONE" -- it does all of that. So, for the last two releases of Oracle -- you have had this capability.
problem inserting timestamp timezone
A reader, October 24, 2002 - 3:30 pm UTC
Hi
I am following some examples from ILT guide and I get this error
SQL> CREATE TABLE times (
2 ts TIMESTAMP
3 , tsz TIMESTAMP WITH TIME ZONE
4 , tlz TIMESTAMP WITH LOCAL TIME ZONE
5 , oldtime DATE );
SQL> INSERT INTO times VALUES
2 ( '21-MAY-01 10:00:00.25 AM'
3 , '21-MAY-01 10:00:00.25 AM CET'
4 , '21-MAY-01 10:00:00.25 AM'
5 , '21-MAY-01' );
( '21-MAY-01 10:00:00.25 AM'
*
ERROR at line 2:
ORA-01830: date format picture ends before converting entire input string
SQL> SELECT DBTIMEZONE, SESSIONTIMEZONE from dual;
DBTIME SESSIONTIMEZONE
------ ---------------------------------------
+02:00 Europe/London
SQL> select * from nls_session_parameters;
PARAMETER VALUE
------------------------------ -------------------------------
NLS_LANGUAGE ENGLISH
NLS_TERRITORY UNITED KINGDOM
NLS_CURRENCY £
NLS_ISO_CURRENCY UNITED KINGDOM
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT YYYY-MON-DD HH24:MI:SS
NLS_DATE_LANGUAGE ENGLISH
NLS_SORT BINARY
NLS_TIME_FORMAT HH24.MI.SSXFF
NLS_TIMESTAMP_FORMAT DD-MON-RR HH24.MI.SSXFF
NLS_TIME_TZ_FORMAT HH24.MI.SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH24.MI.SSXFF TZR
NLS_DUAL_CURRENCY
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
Anything wrong with my session?!?!?
October 24, 2002 - 4:18 pm UTC
it is your nls_timestamp_format. You have it with HH24 -- but you use a string with HH and AM:
ops$tkyte@ORA920.US.ORACLE.COM> alter session set NLS_TIMESTAMP_FORMAT = 'DD-MON-RR HH24.MI.SSXFF';
Session altered.
ops$tkyte@ORA920.US.ORACLE.COM> select localtimestamp from dual;
LOCALTIMESTAMP
---------------------------------------------------------------------------
24-OCT-02 16.22.39.517361
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> INSERT INTO times VALUES
2 ( '21-MAY-01 10:00:00.25 AM'
3 , '21-MAY-01 10:00:00.25 AM CET'
4 , '21-MAY-01 10:00:00.25 AM'
5 , '21-MAY-01' );
( '21-MAY-01 10:00:00.25 AM'
*
ERROR at line 2:
ORA-01830: date format picture ends before converting entire input string
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> alter session set NLS_TIMESTAMP_FORMAT = 'DD-MON-RR HH.MI.SSXFF AM';
Session altered.
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> INSERT INTO times VALUES
2 ( '21-MAY-01 10:00:00.25 AM'
3 , '21-MAY-01 10:00:00.25 AM CET'
4 , '21-MAY-01 10:00:00.25 AM'
5 , '21-MAY-01' );
1 row created.
ops$tkyte@ORA920.US.ORACLE.COM>
wierd result
A reader, October 25, 2002 - 1:00 am UTC
format iw returns wierd result.
My query is as follows:
SQL>select to_char(to_date('&dt','dd-mon-yyyy'),'iw') from dual;
Enter value for dt: 31-dec-2002
old 1: select to_char(to_date('&dt','dd-mon-yyyy'),'iw') from dual
new 1: select to_char(to_date('31-dec-2002','dd-mon-yyyy'),'iw') from dual
TO
--
01
SQL> /
Enter value for dt: 30-dec-2002
old 1: select to_char(to_date('&dt','dd-mon-yyyy'),'iw') from dual
new 1: select to_char(to_date('30-dec-2002','dd-mon-yyyy'),'iw') from dual
TO
--
01 (but weeks belongs to year 2002 here it returns week of year 2003)
SQL> /
Enter value for dt: 29-dec-2002
old 1: select to_char(to_date('&dt','dd-mon-yyyy'),'iw') from dual
new 1: select to_char(to_date('29-dec-2002','dd-mon-yyyy'),'iw') from dual
TO
--
52
So any weekly report taken on 30/31 of dec will show a wrong result?Please discuss.
October 25, 2002 - 6:13 am UTC
depends on the number of weeks in the year -- the ISO guys say some days from last year belong to a week in this year.
it is not wrong. it is in fact correct.
to_char works to_date doesn't
Ram, January 20, 2003 - 7:00 pm UTC
Tom,
When I tried in sql/plsus, I'm getting the following error:
SQL> select to_char(sysdate,'CCYYMMDDHHMMSSmmmmmm') from dual; -----> this works
TO_CHAR(SYSDATE,'CCY
--------------------
21030120030129010101
SQL> select to_date('21030120030129010101','CCYYMMDDHHMMSSmmmmmm') from dual;
select to_date('21030120030129010101','CCYYMMDDHHMMSSmmmmmm') from dual
*
ERROR at line 1:
ORA-01820: format code cannot appear in date input format
My vendor exchanges dates in this format.
How to solve this problem.
thanks
Ram
January 20, 2003 - 7:22 pm UTC
well, just to be clear -- both to_char and to_date are working -- it is just that the format used for OUTPUT cannot be used for INPUT.
you'll have to subtract 100 years I suppose. Maybe if you told me how you need to process this - I would be able to answer better.
ops$tkyte@ORA920> select add_months( to_date(substr('21030120030129010101',1,14),'YYYYMMDDHH24MISS'),
2 -1200 ) from dual
3 /
ADD_MONTH
---------
20-JAN-03
date format
Ram, January 21, 2003 - 1:15 pm UTC
Hi,
The purpose of this is, vendor sends a respone with date and time portion as mentioned in the above format, which we would like to store in our db (date column).
If the date and time I have received is more than what I have, I have to update some info. in that table.
let me know if it's possible.
thanks
January 21, 2003 - 2:23 pm UTC
I gave you the function to do that? now what?
timezone !!
A reader, March 04, 2003 - 1:45 pm UTC
Hi tom,
I know from above discussion 9i is the way to go for timezone info. but i don't have 9i in company.
I have 8i (8.1.7). and our application has needs to store that info and sysc sydny,tokyo, london time,perform calculations and output the data analysis..
so could you please suggest your view to store data for this type of app.
I was thinking to have everything in GMT in time dim. and
create another flake dim for conversion
table 1 : time
--------------
timekey number
...
other time info
table 2 : timezone
-------------------
zone varchar2
name varchar2
converzion_fn varchar2
sample values
zone name converzion_fn
EST EST GMT-5 or (timekey -5)
what do you say ?
any better solution for 8.1.7 ?
any solution to above question ?
A reader, March 04, 2003 - 9:30 pm UTC
March 05, 2003 - 9:04 am UTC
My gosh -- give me a brief moment of time to put it together sometimes!
Ok, we went back to our 817 code for a web calendar we built that needed timezone support. We did code up a package with extensive timezone support:
</code>
http://asktom.oracle.com/~tkyte/timedate.zip <code>
It was authored by Joel Kallman and covers pretty much the globe as far as we know. The main routine after you install is:
create or replace package timedate_pkg
as
--
-- For any installation, the following package global variable
-- should be set to the locale of the server where this package
-- is installed (e.g., '1' implies that the database is running in
-- timezone/locale America/New York)
--
g_server_locale constant number := 1;
This converts a date into GMT -- you get the p_locale numbers from the ID column of the TIMEDATE_LOCALES$ tablefunction to_gmt_date(
p_local_date in date,
p_locale in number )
return date;
Converts gmt to some other timezonefunction to_local_date(
p_gmt_date in date,
p_locale in number )
return date;
sort of like SYSDATE but returns SYSDATE adjusted to be in the timezone you ask forfunction cur_local_date(
p_locale in number )
return date;
obviousfunction cur_gmt_date
return date;
--
end timedate_pkg;
/
Thanks !!
A reader, March 07, 2003 - 4:10 pm UTC
thanks tom it's great !!
I am late to check back ain't i ?
This is great thanks !!
Looking for examples
Sikandar Hayat, April 23, 2003 - 1:30 pm UTC
April 23, 2003 - 7:49 pm UTC
timestamp -- that doesn't have a timezone. It is much like DATE is in Oracle8i except it supports fractional seconds.
When you insert the time, the timezone is not saved with it -- it is just a "timestamp". It'll be a value like "Wed April 4, 2003 10:05:02.123456 am"
A timestamp with timezone stores the timezone as well -- meaning if you were in California and I was in Virginia (i am, but I've no clue what TZ pakistan is in ;) and we both inserted that same time, yours would be:
Wed April 4, 2003 10:05:02.123456 am PACIFIC
and mine would be:
Wed April 4, 2003 10:05:02.123456 am EASTERN
and if we subtracted them from eachother -- the difference would be 3 hours since that is the difference between our time zones. If we just used timestamp (without timezone) it would have be "0 hours" difference.
With LOCAL time zone just means that
o Data is normalized to the database time zone when it is stored in the database.
o When the data is retrieved, users see the data in the session time zone.
consider (my database is US/Eastern by the way...)
ops$tkyte@ORA920.US.ORACLE.COM> create table t ( x timestamp, y timestamp with time zone, z timestamp with local time zone )
2 /
Table created.
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> alter session set time_zone = 'US/PACIFIC'
2 /
Session altered.
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> insert into t values ( systimestamp, systimestamp, systimestamp );
1 row created.
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> select * from t;
X
---------------------------------------------------------------------------
Y
---------------------------------------------------------------------------
Z
---------------------------------------------------------------------------
23-APR-03 07.47.16.451473 PM
23-APR-03 07.47.16.451473 PM -04:00
23-APR-03 04.47.16.451473 PM
X is without timezone
Y is with timezone and that is 7.47 with a GMT minus 4 offset (4 hours from GMT)
Z is with timezone and the 7.47 that was inserted was "normalized", or input as eastern time (since systimestamp is the SERVERS time) and upon retrieval converted to MY timezone -- pacific -- for me.
ops$tkyte@ORA920.US.ORACLE.COM> delete from t;
1 row deleted.
ops$tkyte@ORA920.US.ORACLE.COM> insert into t values ( localtimestamp, localtimestamp, localtimestamp );
1 row created.
ops$tkyte@ORA920.US.ORACLE.COM> select * from t;
X
---------------------------------------------------------------------------
Y
---------------------------------------------------------------------------
Z
---------------------------------------------------------------------------
23-APR-03 04.50.06.474172 PM
23-APR-03 04.50.06.474172 PM US/PACIFIC
23-APR-03 04.50.06.474172 PM
these all come out the same since I used my own timezone but...
ops$tkyte@ORA920.US.ORACLE.COM> alter session set time_zone = 'US/CENTRAL';
Session altered.
ops$tkyte@ORA920.US.ORACLE.COM> select * from t;
X
---------------------------------------------------------------------------
Y
---------------------------------------------------------------------------
Z
---------------------------------------------------------------------------
23-APR-03 04.50.06.474172 PM
23-APR-03 04.50.06.474172 PM US/PACIFIC
23-APR-03 06.50.06.474172 PM
you can see the difference now with the timezone vs local timezone...
One more
Sikandar Hayat, April 24, 2003 - 8:11 am UTC
Great examples to understand timestamp feature. Now one more question is if in "Timestamp with Local time zone" there is no zone
LOCALTIMESTAMP
------------------------------
24-APR-03 05.06.36.000001 PM
So how it is recognizing on client and giving the correct time or it is placing time zone info hidden.
Say on the server I have set time zone with '-05:00' while on client it is '+05:00'.
April 24, 2003 - 9:26 am UTC
clients have a timezone (see my alter session) and the database has a timezone.
The database takes the date in its timezone and converts it to whatever timezone the client asked for (has set)
that is the reason for the difference when I set the client timezone to US/Central for example -- the database returned the timestamp in a time relative to MY local timezone -- adjusted from the databases timezone.
Great...
Sikandar Hayat, April 24, 2003 - 12:20 pm UTC
Thanks TOM I got your point and timestamp concept.
A reader, June 02, 2003 - 9:11 am UTC
Hi Tom,
What is the differene between systimestamp and localtimestamp function ?
Thanks
v$timezone_names
phil, June 23, 2003 - 2:13 am UTC
in the view v$timezone_names , what does the TZABBREV actually represent ?
how can America/New_York and Australia/Sydney be EST ?
if i wanted to create a record with a "timestamp with time zone" for sydney ... how can i use the abbreviation. When I insert a record with an EST timezone the db tells me that I am -4 hours .... which is not sydney !
thanks
June 23, 2003 - 8:14 am UTC
Australia uses it too. is the the abbreviation and there are many "duplicates". It takes a locale and a tz abbreviation to know what is really means.
suggestion -- use long names to avoid any sense of confusion.
ora-01830
Tatiane, July 23, 2003 - 4:36 pm UTC
Tom, I am getting an intermittent ora-01830 when I run the code below. It may run several times without an error, but sometimes there it is, for the SAME data.
There are occasions when I run it, the error comes up -- I reload the page (using the refresh button on the browser) and then the page appears without the error. Very strange, isn't it ?
This code has run in development, uat and this error happens only in production. They are all 9.2.0.1. I searched Metalink for some clue, but I was not able to find anything suggestive.
This code generated the error:
BEGIN pkg_cad_eqpto.insere_eqpto( '2', 'prm - safs2', '192.252.250.090', '', '', '23/07/2003', '1', 'PGR/SAFS', '46', '1342', '921', '1342', '', '', '1', '3', '165', :id_eqpto, :cod_erro, :msg_erro); END;
CREATE OR REPLACE PACKAGE BODY "API_EQUIP"."PKG_CAD_EQPTO" is
-- ----- insere_eqpto -----------
procedure insere_eqpto
(
p_id_status number,
p_nome varchar2,
p_ip varchar2,
p_ip_nat varchar2,
p_end_no varchar2,
p_d_ativ varchar2,
p_id_tipo number,
p_sig_centro varchar2,
p_id_modelo number,
p_id_propriet number,
p_id_cliente number,
p_id_emp_manut number,
p_versao_sw varchar2,
p_obs varchar2,
p_caract varchar2,
p_finalidade varchar2,
p_login varchar2,
p_id_eqpto out number,
p_cod out number,
p_msg out varchar2
)
is
v_caract pkg_global.table_of_number:=pkg_global.table_of_number();
v_finalidade pkg_global.table_of_number:=pkg_global.table_of_number();
v_d_ativ date := to_date(p_d_ativ,'DD/MM/YYYY');
v_id_eqpto number;
v_id_op number;
v_id_eqpto_hist number;
v_cnt number;
nome_existe exception;
begin
.
.
.
Apparently, the error ocurrs in:
v_d_ativ date := to_date(p_d_ativ,'DD/MM/YYYY');
Thanks for your help.
July 23, 2003 - 9:28 pm UTC
no versions or anything -- but -- contact support for this one.
ora-01830
Tatiane, July 24, 2003 - 9:12 am UTC
I said above:
They are all 9.2.0.1.
You said above:
no versions or anything -- but -- contact support for this one.
:(
one more question
bachina, July 24, 2003 - 10:23 am UTC
i am getting the date as yyyyww i.s 200330 (year and week of the year) i want to change it to ddmmyyyy where ddmm are the first date of that week and month is the month in which that week is?
can you help me in this regard
July 24, 2003 - 4:17 pm UTC
PLEASE, test this -- here are 3 different ways - they'll all behave differently on boundary values, TEST this to make sure the one you pick is the one that works:
ops$tkyte@ORA920LAP> column d new_val D
ops$tkyte@ORA920LAP> select to_char( sysdate, 'yyyyww' ) d from dual;
D
------
200330
ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> select trunc(to_date( substr('&d',1,4), 'yyyy' ), 'y') from dual;
old 1: select trunc(to_date( substr('&d',1,4), 'yyyy' ), 'y') from dual
new 1: select trunc(to_date( substr('200330',1,4), 'yyyy' ), 'y') from dual
TRUNC(TO_
---------
01-JAN-03
ops$tkyte@ORA920LAP> select trunc(trunc( to_date( substr('&d',1,4), 'yyyy' ),'y') + (substr('&d',5)-1)*7,'w') from dual;
old 1: select trunc(trunc( to_date( substr('&d',1,4), 'yyyy' ),'y') + (substr('&d',5)-1)*7,'w') from dual
new 1: select trunc(trunc( to_date( substr('200330',1,4), 'yyyy' ),'y') + (substr('200330',5)-1)*7,'w') from dual
TRUNC(TRU
---------
22-JUL-03
ops$tkyte@ORA920LAP> select trunc(trunc( to_date( substr('&d',1,4), 'yyyy' ),'y') + (substr('&d',5)-1)*7,'iw') from dual;
old 1: select trunc(trunc( to_date( substr('&d',1,4), 'yyyy' ),'y') + (substr('&d',5)-1)*7,'iw') from dual
new 1: select trunc(trunc( to_date( substr('200330',1,4), 'yyyy' ),'y') + (substr('200330',5)-1)*7,'iw') from dual
TRUNC(TRU
---------
21-JUL-03
ops$tkyte@ORA920LAP> select next_day(trunc( to_date( substr('&d',1,4), 'yyyy' ),'y') + (substr('&d',5)-2)*7,'sun') from dual;
old 1: select next_day(trunc( to_date( substr('&d',1,4), 'yyyy' ),'y') + (substr('&d',5)-2)*7,'sun') from dual
new 1: select next_day(trunc( to_date( substr('200330',1,4), 'yyyy' ),'y') + (substr('200330',5)-2)*7,'sun') from dual
NEXT_DAY(
---------
20-JUL-03
restricting the week form 1-52
srinivasa rao bachina, July 25, 2003 - 5:38 am UTC
Hi Tom,
Thanks for quick reply,
my req will suit with
select trunc(trunc( to_date( substr('&d',1,4), 'yyyy'
),'y') + (substr('&d',5)-1)*7,'iw') from dual;
and one more constraint in that is the week should be in bet 1-52.
if we give 209972 it should give the error,but is returning 10/5/2100
can you please help me in this case also?
July 25, 2003 - 7:09 am UTC
add a CASE or DECODE statement in there to do that
case when substr( '&d',5) between 1 and 52 then trunc(trunc(.....)
else to_date( '99999999', 'mmddyyyy' )
end
for example
A reader, August 05, 2003 - 3:24 pm UTC
current_timestamp
pasko, November 10, 2003 - 3:40 pm UTC
Hi Tom,
thanks for a Great thread above..
I have a similar problem , whereby a coleague of mine had coded something like this:
------
select to_char( current_timestamp , 'YYYY-MM-DD HH24:MI:SS.FF') into l_local_variable
from dual ;
------
First of all, why do you discourage selecting from Dual, like my colleague did above, because
I have read in your site that ,
the above should be coded as follows in the Declaration Section:
------
--i want Timestamp as a String because this will be padded --with some other Characters later:
l_local_variable varchar2(32) :=
to_char( current_timestamp , 'YYYY-MM-DD HH24:MI:SS.FF') ;
-----
So , whats the trouble with the selecting from Dual ?
--
But my Real problem is that there are different clients which are connecting to Oracle 9iR2 Database from different machines, all located also in Germany...
and we have observed that , this function stores wrong Timestamps..sometimes 1 Hour ahead.
So i read the Docs and told him that current_timestamp is also influenced by session or Database Time zones and that he should have used systimestamp instead.
I tried to test by connecting through all the Clients locally using sqlplus , but i couldn't reproduce the wrong timestamps.
So , we were wondering where the this error could be coming from.
If the client Time-Zones are not set, how does this function behave ?
Does it take the dbtimezone instead?
Actually , after we get the timestamp value , we have to further convert it to GMT , taking into account the daylight savings...
I have searched for some Time-Zone Names from the View :
v$timezone_names , but could not find the one suitable for Germany.
In JAVA, i had once used Time-Zone 'Europe/Berlin', but this is not listed in the View.
Do you have any idea as to what Time-Zone should be used in Germany ?
I want to later use the Function : FROM_TZ to convert my Timestamps to GMT.
I hope you will consider my Question.
Thanks in advance.
With Best Regards.
November 10, 2003 - 4:01 pm UTC
when a direct assignment would work:
x := y;
why would you confuse the issue by selecting it?
select y into x from dual;
when:
x := y;
is what you want -- that is what you should code.
My server is in EST, my client is faking to be in PST:
[tkyte@tkyte-pc tkyte]$ echo $TZ
PST
[tkyte@tkyte-pc tkyte]$ sqlplus scott/tiger@ora920
SQL*Plus: Release 9.2.0.4.0 - Production on Mon Nov 10 20:57:18 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
scott@ORA920PC> select current_timestamp, systimestamp from dual;
CURRENT_TIMESTAMP
---------------------------------------------------------------------------
SYSTIMESTAMP
---------------------------------------------------------------------------
10-NOV-03 08.57.22.615300 PM +00:00
10-NOV-03 03.57.22.615241 PM -05:00
scott@ORA920PC> select to_char( current_timestamp , 'YYYY-MM-DD HH24:MI:SS.FF'),
2 to_char( systimestamp, 'YYYY-MM-DD HH24:MI:SS.FF') from dual;
TO_CHAR(CURRENT_TIMESTAMP,'YY TO_CHAR(SYSTIMESTAMP,'YYYY-MM
----------------------------- -----------------------------
2003-11-10 20:57:42.347983 2003-11-10 15:57:42.347958
See the differences -- BUT -- if i use a direct connect (fork/exec the dedicated server), it picks up my TZ:
[tkyte@tkyte-pc tkyte]$ echo $TZ
PST
[tkyte@tkyte-pc tkyte]$ plus
SQL*Plus: Release 9.2.0.4.0 - Production on Mon Nov 10 20:58:30 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
ops$tkyte@ORA920PC> select dbtimezone from dual;
DBTIME
------
-05:00
ops$tkyte@ORA920PC> select current_timestamp, systimestamp from dual;
CURRENT_TIMESTAMP
---------------------------------------------------------------------------
SYSTIMESTAMP
---------------------------------------------------------------------------
10-NOV-03 08.58.49.140554 PM +00:00
10-NOV-03 08.58.49.140502 PM +00:00
<b>so, make sure you use a dedicated server with the "right" TZ when you are testing</b>
not sure what TZ you want to use in germany -- do the 3 character codes look familar? do you use daylight savings (if not, the Etc/GMT-n ones look good)
what if the $TZ is not set
pasko, November 11, 2003 - 7:38 am UTC
Hi Tom,
Thank you very much for the above Response..
Actually, i also wanted to know how would the current_timestamp behave when my $TZ is not set .
Would it then consider the dbtimezone ?
I note that my dbtimezone is false : it should be +01:00
SQL> select sessiontimezone , dbtimezone from dual ;
SESSIONTIMEZONE
---------------------------------------------------------------------------
DBTIME
------
+01:00
+02:00
In Germany , we use Time-Zone MEZ.
Some machines have :
CET for Central European Time or
MET for Mid-European Time
Example :
--Our $TZ is not set
SQL> host echo $TZ
SQL> host date
Tue Nov 11 13:22:53 MET 2003
--in another machine,a few minutes later ,
SQL> host date
Tue Nov 11 13:26:18 CET 2003
SQL>
Another question , is that, would the Daylight Savings Time be taken into account , if i set the DBTIMEZONE explicitly with the alter database command ?
I have seen the View v$timezone_names has 'MET' and 'CET' in it , but i am not sure if this will take into account the Daylight Savings Time and also not sure which one to use if i want to set the dbtimezone for Germany.
Thanks in advance.
Regards.
November 11, 2003 - 8:07 am UTC
we get it from the OS -- whatever the OS reports back to us as the time, thats the time.
for dst to work, you use a timezone setting that obeys dst, then it just happens, you need not set anything.
sorry - i'm not a "timezone expert" at all -- don't follow all of the rules and such. if MET or CET automagically flips times on your computers OS clock, they would be what you want. You might want to talk to someone who understands the nuances of timezones in your area of the world.
Date format picture ends before converting
Jamil, January 13, 2004 - 6:00 am UTC
Dear Sir
I am getting this message when I run my report
REP-0613: value does not match mask DD/MM/YYYY
ORA-01830: Date format picture ends before converting entries input string
Please tell me how can I solve this problem
Best regards
Jamil
January 13, 2004 - 5:37 pm UTC
make sure your date format picture does not end before converting the string...
it means your input string -- is not in the format dd/mm/yyyy
ops$tkyte@ORA9IR2> select to_date( '01/01/2004 12', 'dd/mm/yyyy' ) from dual;
select to_date( '01/01/2004 12', 'dd/mm/yyyy' ) from dual
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string
(EG: you have bad data)
time zone question
James Su, February 09, 2004 - 5:43 pm UTC
hi Tom,
I am using Oracle 9i, and I have some date type columns (not timestamp type) storing GMT datetimes. My users log on from different time zones, how can I provide a view for them to see these columns displayed as their local time(also date type,not timestamp)? Daylight savings should be taken in account. I am thinking of adding a time zone column to my user table and assign it to a package value when a user logs on. What kind of data should I store in this time zone column?
Thank you!
solution in 9i
James Su, February 11, 2004 - 3:14 pm UTC
CREATE OR REPLACE FUNCTION to_localtime(
p_date in date, -- GMT datetime
p_tzr in varchar2 -- timezone name
) return date -- local datetime
AS
v_localts TIMESTAMP WITH TIME ZONE;
BEGIN
v_localts:= FROM_TZ(CAST(p_date AS TIMESTAMP),'Greenwich') AT TIME ZONE p_tzr;
return to_date(to_char(v_localts,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS');
END to_local;
/
hi Tom,
the package is great, but table timedate_locales$ does not
contain a complete list timezone names as V$TIMEZONE_NAMES.TZNAME. since I am using 9i, I wrote a function for conversion.
9i date conversion
A reader, April 20, 2004 - 4:42 am UTC
Hi Tom,
sorry - I am a little confused, I should be able to work this out from the this thread.
what is the simplist generic way in 9i to convert a date that is stored in UTC to a nominated date in a different timezone? My dates are stored as dates and not with timezones.
date1 is 07-Apr-2004 23:00:00
I would like to apply for example Australian Eastern Standard Time (+10) to it and the new date should be 08-Apr-2004. Or Eastern Daylight Saving Time (-4) would be still 07-Apr-2004.
April 20, 2004 - 8:43 am UTC
read about new_time(). builtin function. limited timezone support, but it might be enough.
if it is not enough, search for timedate.zip on this page for a plsql package we wrote to provide the support.
Mark, April 21, 2004 - 5:08 pm UTC
Tom, I have two questions on Timestamp w/ Timezone
First: Here is my env.
Win 2000 Oracle 9.2.0.4
SQL> select * from NLS_SESSION_PARAMETERS;
PARAMETER VALUE
--------------------------- -----------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZH:TZM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZH:TZM
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
17 rows selected.
1) how do you do date math without losing Timezone
i.e
SQL> DESC TEST_TIME;
Name Type
------------------- ----------------------------
TZ VARCHAR2(25)
NEWDATE TIMESTAMP(6) WITH TIME ZONE
select to_char(newdate,'DD-MON-YYYY HH24:MI:SS TZD') from test_time;
TO_CHAR(NEWDATE,'DD-MON-YYY
---------------------------
21-APR-2004 10:18:56 EDT
21-APR-2004 09:19:12 CDT
21-APR-2004 08:19:44 MDT
21-APR-2004 07:19:58 PDT
SQL> insert into test_time (select TZ, newdate + 1 from test_time);
4 rows created.
SQL> select to_char(newdate,'DD-MON-YYYY HH24:MI:SS TZD') from test_time;
TO_CHAR(NEWDATE,'DD-MON-YYY
---------------------------
21-APR-2004 10:18:56 EDT
21-APR-2004 09:19:12 CDT
21-APR-2004 08:19:44 MDT
21-APR-2004 07:19:58 PDT
22-APR-2004 10:18:56
22-APR-2004 09:19:12
22-APR-2004 08:19:44
22-APR-2004 07:19:58
-- The four new records lost their timezone
2) When selecting a column of "timestamp with time zone"
Do you have to use a to char, or is there a defaul format like for date.
SQL> DESC TEST_TIME_OLD
Name Type
------------------------------------------------
TZ VARCHAR2(25)
OLD_DATE DATE
SQL> SELECT OLD_DATE FROM TEST_TIME_OLD;
OLD_DATE
---------
21-APR-04
SQL> SELECT NEWDATE FROM TEST_TIME;
ERROR:
ORA-01877: string is too long for internal buffer
Thanks
April 21, 2004 - 9:26 pm UTC
when you run this, what happens
ops$tkyte@ORA9IR2> create table t ( tz varchar2(25), newdate timestamp(6) with time zone );
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t
2 select 'xx', localtimestamp
3 from dual;
1 row created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select to_char(newdate,'DD-MON-YYYY HH24:MI:SS TZD') from t;
TO_CHAR(NEWDATE,'DD-MON-YYY
---------------------------
21-APR-2004 21:27:54 EDT
ops$tkyte@ORA9IR2> insert into t select tz, newdate+1 from t;
1 row created.
ops$tkyte@ORA9IR2> select to_char(newdate,'DD-MON-YYYY HH24:MI:SS TZD') from t;
TO_CHAR(NEWDATE,'DD-MON-YYY
---------------------------
21-APR-2004 21:27:54 EDT
22-APR-2004 21:27:54 EDT
Mark, April 26, 2004 - 11:43 am UTC
That worked:
SQL> create table t ( tz varchar2(25), newdate timestamp(6) with
2 time zone );
Table created.
SQL> insert into t
2 select 'xx', localtimestamp
3 from dual;
1 row created.
SQL> select to_char(newdate,'DD-MON-YYYY HH24:MI:SS TZD') from t;
TO_CHAR(NEWDATE,'DD-MON-YYY
---------------------------
26-APR-2004 10:26:07 CDT
SQL> insert into t select tz, newdate+1 from t;
1 row created.
SQL> select to_char(newdate,'DD-MON-YYYY HH24:MI:SS TZD') from t;
TO_CHAR(NEWDATE,'DD-MON-YYY
---------------------------
26-APR-2004 10:26:07 CDT
27-APR-2004 10:26:07 CDT
Not sure what the problem was before. Another thing you could clear up for me is the TZ formating. Take a look at this:
1 declare
2 v_tz1 timestamp with time zone;
3 v_tz2 timestamp with time zone;
4 v_tz3 timestamp with time zone;
5 v_str1 varchar2(100);
6 v_str2 varchar2(100);
7 v_x varchar2(100) := 'DD-MON-YYYY HH24:MI:SS';
8 begin
9 -- CREATE THREE DIFF OBJ WITH THE SAME DATE,TIME AND TIMEZONE
10 v_tz1 := to_timestamp_tz(to_char(sysdate,v_x)||' US/Central',v_x||' TZR');
11 v_tz2 := to_timestamp_tz(to_char(sysdate,v_x)||' CDT',v_x||' TZD');
12 v_tz3 := to_timestamp_tz(to_char(sysdate,v_x)||' -05:00',v_x||' TZH:TZM');
13 -- DISPLAY THE OBJECTS IN DIFFERENT WAYS
14 dbms_output.put_line('DATA FOR TZ1 Created using US/Central');
15 dbms_output.put_line('Time Zone = '||TO_CHAR(v_tz1,'TZR'));
16 dbms_output.put_line('Time Zone = '||TO_CHAR(v_tz1,'TZD'));
17 dbms_output.put_line('Time Zone = '||TO_CHAR(v_tz1,'TZH:TZM'));
18 dbms_output.put_line('-----------------------------------------------');
19 dbms_output.put_line('DATA FOR TZ2 Created using CDT');
20 dbms_output.put_line('Time Zone = '||TO_CHAR(v_tz2,'TZR'));
21 dbms_output.put_line('Time Zone = '||TO_CHAR(v_tz2,'TZD'));
22 dbms_output.put_line('Time Zone = '||TO_CHAR(v_tz2,'TZH:TZM'));
23 dbms_output.put_line('-----------------------------------------------');
24 dbms_output.put_line('DATA FOR TZ3 Created using -05:00');
25 dbms_output.put_line('Time Zone = '||TO_CHAR(v_tz3,'TZR'));
26 dbms_output.put_line('Time Zone = '||TO_CHAR(v_tz3,'TZD'));
27 dbms_output.put_line('Time Zone = '||TO_CHAR(v_tz3,'TZH:TZM'));
28* end;
SQL> /
DATA FOR TZ1 Created using US/Central
Time Zone = US/CENTRAL
Time Zone = CDT
Time Zone = -05:00
-----------------------------------------------
DATA FOR TZ2 Created using CDT
Time Zone = US/CENTRAL
Time Zone = CDT
Time Zone = -05:00
-----------------------------------------------
DATA FOR TZ3 Created using -05:00
Time Zone = -05:00
Time Zone =
Time Zone = -05:00
Can you help me understand why I can't pull the TZName 'US/Central' from a "Timestamp with time zone" when it is created using TZH:TZM?
Thanks
(DB 9.2.0.4)
I'm Sorry
Mark, April 26, 2004 - 12:03 pm UTC
Tom, Please disregard the above. It was from a 9.2.0.1 Database.
My 9.2.0.4 and 9.2.05 Databases both fail your test.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
SQL> create table t ( tz varchar2(25), newdate timestamp(6) with
2 time zone );
Table created.
SQL>
SQL> insert into t
2 select 'xx', localtimestamp
3 from dual;
1 row created.
SQL>
SQL>
SQL> select to_char(newdate,'DD-MON-YYYY HH24:MI:SS TZD') from t;
TO_CHAR(NEWDATE,'DD-MON-YYY
---------------------------
26-APR-2004 11:00:18
SQL>
SQL>
SQL> insert into t select tz, newdate+1 from t;
1 row created.
SQL>
SQL>
SQL> select to_char(newdate,'DD-MON-YYYY HH24:MI:SS TZD') from t;
TO_CHAR(NEWDATE,'DD-MON-YYY
---------------------------
26-APR-2004 11:00:18
27-APR-2004 11:00:18
Also the test for TO_CHAR give the following on both.
SQL> declare
2 v_tz1 timestamp with time zone;
3 v_tz2 timestamp with time zone;
4 v_tz3 timestamp with time zone;
5 v_str1 varchar2(100);
6 v_str2 varchar2(100);
7 v_x varchar2(100) := 'DD-MON-YYYY HH24:MI:SS';
8 begin
9 -- CREATE THREE DIFF OBJ WITH THE SAME DATE,TIME AND TIMEZONE
10 v_tz1 := to_timestamp_tz(to_char(sysdate,v_x)||' US/Central',v_x||' TZR');
11 v_tz2 := to_timestamp_tz(to_char(sysdate,v_x)||' CDT',v_x||' TZD');
12 v_tz3 := to_timestamp_tz(to_char(sysdate,v_x)||' -05:00',v_x||' TZH:TZM');
13 -- DISPLAY THE OBJECTS IN DIFFERENT WAYS
14 dbms_output.put_line('DATA FOR TZ1 Created using US/Central');
15 dbms_output.put_line('Time Zone = '||TO_CHAR(v_tz1,'TZR'));
16 dbms_output.put_line('Time Zone = '||TO_CHAR(v_tz1,'TZD'));
17 dbms_output.put_line('Time Zone = '||TO_CHAR(v_tz1,'TZH:TZM'));
18 dbms_output.put_line('-----------------------------------------------');
19 dbms_output.put_line('DATA FOR TZ2 Created using CDT');
20 dbms_output.put_line('Time Zone = '||TO_CHAR(v_tz2,'TZR'));
21 dbms_output.put_line('Time Zone = '||TO_CHAR(v_tz2,'TZD'));
22 dbms_output.put_line('Time Zone = '||TO_CHAR(v_tz2,'TZH:TZM'));
23 dbms_output.put_line('-----------------------------------------------');
24 dbms_output.put_line('DATA FOR TZ3 Created using -05:00');
25 dbms_output.put_line('Time Zone = '||TO_CHAR(v_tz3,'TZR'));
26 dbms_output.put_line('Time Zone = '||TO_CHAR(v_tz3,'TZD'));
27 dbms_output.put_line('Time Zone = '||TO_CHAR(v_tz3,'TZH:TZM'));
28 end;
29 /
DATA FOR TZ1 Created using US/Central
Time Zone = US/CENTRAL
Time Zone = CDT
Time Zone = -05:00
-----------------------------------------------
DATA FOR TZ2 Created using CDT
Time Zone = -05:00
Time Zone =
Time Zone = -05:00
-----------------------------------------------
DATA FOR TZ3 Created using -05:00
Time Zone = -05:00
Time Zone =
Time Zone = -05:00
PL/SQL procedure successfully completed.
Again here are my NLS_SESSION_PARAMETERS
SQL> select * from NLS_SESSION_PARAMETERS;
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZH:TZM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZH:TZM
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
Thanks for your help
April 26, 2004 - 2:24 pm UTC
i won't be able to look at this for a while (traveling in iceland, don't have an answer off of the top of my head, not able to reproduce in my 9204 instance so it is not the release in as much as something different between your 3 instances. compare them and see what you see.
Randall, April 28, 2004 - 11:48 am UTC
Tom, Maybe I am missing something, but this looks like a bug.
Using the TZNAME from V$TIMEZONE_NAMES and the TZ_OFFSET function.
Offset for 'US/Pacific' correct..
SQL> select TZ_OFFSET('US/Pacific') FROM DUAL;
TZ_OFFS
-------
-07:00
Offset for 'Asia/Hong_Kong' correct..
SQL> select TZ_OFFSET('Asia/Hong_Kong') FROM DUAL;
TZ_OFFS
-------
+08:00
But using the TZNAME 'ETC/GMT+8' returns the following:
SQL> select TZ_OFFSET('ETC/GMT+8') FROM DUAL;
TZ_OFFS
-------
-08:00
SQL> select TZ_OFFSET('ETC/GMT-8') FROM DUAL;
TZ_OFFS
-------
+08:00
Is this wrong or am I missing something.
Database is 9.2.0.4
Thanks
April 28, 2004 - 7:06 pm UTC
i don't know, I don't know what "etc/gmt+8" means personally. sorry....
not a timezone expert. I just look at the clocks when I land at the airport and change my watch :)
according to
http://www.gsp.com/support/virtual/admin/unix/tz/gmt/
I'm not so sure. At 19:00pm EDT on April 28, that page said
Etc/GMT+8 Wed-28-Apr-2004 15:00:38 GMT+8
Etc/GMT-0 Wed-28-Apr-2004 23:00:38 GMT
Etc/GMT-8 Thu-29-Apr-2004 07:00:38 GMT-8
so, GMT+8 was 8 hours BEFORE gmt and GMT-8 was 8 hours *after* gmt. That would be -8 and +8 repectively... the database agreed:
1* alter session set time_zone ='ETC/GMT-8'
ops$tkyte@ORA9IR2> /
Session altered.
ops$tkyte@ORA9IR2> select localtimestamp from dual;
LOCALTIMESTAMP
------------------------------------------------------------------------
29-APR-04 07.04.16.913456 AM
ops$tkyte@ORA9IR2> alter session set time_zone = 'GMT';
Session altered.
ops$tkyte@ORA9IR2> select localtimestamp from dual;
LOCALTIMESTAMP
------------------------------------------------------------------------
28-APR-04 11.04.47.495581 PM
ops$tkyte@ORA9IR2> alter session set time_zone = 'ETC/GMT+8';
Session altered.
ops$tkyte@ORA9IR2> select localtimestamp from dual;
LOCALTIMESTAMP
------------------------------------------------------------------------
28-APR-04 03.05.25.597151 PM
Same issue as Mark, can't get TZR or TZD to display
Justis, May 27, 2004 - 5:27 pm UTC
I am seeing a similar issue to Mark where using a date format of TZD or TZR does not display the correct format string.
HP-UX B.11.00 U 9000/800
SQL*Plus: Release 9.2.0.1.0 - Production on Thu May 27 13:26:32 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> select * from nls_session_parameters
2 /
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
17 rows selected.
SQL> create table time01(
2 d date,
3 ts timestamp,
4 tstz timestamp with time zone,
5 tsltz timestamp with local time zone
6 )
7 /
Table created.
SQL> insert into time01 values( sysdate, systimestamp, systimestamp, systimestamp )
2 /
1 row created.
SQL> column ts format a35
SQL> column tstz format a35
SQL> column tsltz format a35
SQL> select * from time01
2 /
D
---------
TS
-----------------------------------
TSTZ
-----------------------------------
TSLTZ
-----------------------------------
27-MAY-04
27-MAY-04 01.54.06.271429 PM
27-MAY-04 01.54.06.271429 PM -07:00
27-MAY-04 01.54.06.271429 PM
SQL> drop table time01
2 /
Table dropped.
SQL> select to_char(systimestamp,'tzd tzr tzh:tzm') from dual
2 /
TO_CHAR(SYSTIMESTAMP,'TZDTZRTZH:TZM')
-----------------------------------------------
-07:00 -07:00
May 27, 2004 - 8:53 pm UTC
i hate timezones but -- what where you expecting to see?
Expectations
Justis, May 28, 2004 - 6:31 pm UTC
Oracle Docs:
<quote>
TZD Yes Daylight savings information. The TZD value is an abbreviated time zone string with daylight savings information. It must correspond with the region specified in TZR.
Example: PST (for US/Pacific standard time); PDT (for US/Pacific daylight time).
TZR Yes Time zone region information. The value must be one of the time zone regions supported in the database.
Example: US/Pacific
</quote>
According to the documentation, I guess I was expecting to see PST for TZD and US/Pacific or America/Los Angeles or something like that for TZR. Perhaps those format strings are only used for converting a literal to a timestamp with timezone. No business problem here, just trying to gain knowledge.
May 28, 2004 - 8:13 pm UTC
but you have offsets from GMT, not timezones -- you don't seem to be using timezones, but offsets?
-5:00 - what is that? East Coast time or parts of Indiana during the winter?
Resolved
Justis, May 28, 2004 - 7:16 pm UTC
After looking through the documentation a bit more I came across
<quote>
The ambiguity arises because the time zone numerical offset is provided in the expression, and that numerical offset may map to more than one time zone region.
</quote>
which explains why the time zone names can't be displayed. I think this applies to Mark's case above also.
May 28, 2004 - 8:16 pm UTC
(and i didn't even read this before commenting above... :)
TIMESTAMP WITH LOCAL TIME ZONE
Raju, November 09, 2004 - 11:46 pm UTC
Tom,
is "TIMESTAMP WITH LOCAL TIME ZONE" data type
useful only for client server applications..? because in web applications, as clients(from the browser) don't make direct database calls, only the application server makes the database calls, so we always get the datetime of application server's timezone.
please correct if i am wrong..
thanks as always.
November 10, 2004 - 7:04 am UTC
well, there are alter sessions so that clients connected to a middle tier could have their local timezone set for them (eg: your application says "oh, it is tom, tom lives on the east coast, set tom's timezone to X" -- and laster it says "oh, it is mary, she lives on the west coast......")
so, no, I don't think it is only useful in client server.
Time in milliseconds
Patrick, November 18, 2004 - 5:06 pm UTC
Thanks Tom for your help, this web site is the best.
And now the question.
We are using Oracle with java and we want to keep the GMT time in the database. I know that we can use timestamp with timezone to keep specific time from all around the world, but is there an easy way with Oracle to convert time in milliseconds in a readable format.
(In java, the start point of the time in milliseconds is 01-JAN-1970)
For example:
to_date( timeInMilliSec, 'YYYY-MM-DD HH24:MI:SS' ) or another function.
November 19, 2004 - 10:02 am UTC
why would you want to do something so wrong?
the timestamp data type stores the data with the precision you need, please use the correct and proper datatype.
Client vs server time
A reader, April 05, 2005 - 11:31 am UTC
So if a user in California connects to a Oracle database in NY and does
select localtimetamp,systimestamp,sysdate from dual;
Shoudnt the first one (localtimestamp) be 3 hours behind the other 2? Thats not what I see.
What am I missing? Thanks
April 05, 2005 - 12:34 pm UTC
they should see the setting their client set for local, the setting for the database from the database.
I'm in central right now, my database in eastern.
but unless I actually setup my client and db timezones, oracle doesn't know that.
A reader, April 05, 2005 - 1:07 pm UTC
Not sure what you mean by setting. What exactly should I set where?
April 05, 2005 - 6:41 pm UTC
Insert timestamp
Maverick, June 09, 2005 - 3:16 pm UTC
Tom, I am trying to insert a timestamp value(with milli seconds) into a timestamp column and i'm getting error message
How do i insert timestamp with this kind of input?
myschema@myDB10G> create table test(mytime timestamp);
Table created.
myschema@myDB10G> insert into test values
2 ('09-Jun-2005 12:34:45.56');
1 row created.
myschema@myDB10G> insert into test values (to_date('06/09/2005 12:34:45.56','mm/dd/yyyy hh:mi:ss.ff
'));
insert into test values (to_date('06/09/2005 12:34:45.56','mm/dd/yyyy hh:mi:ss.ff'))
*
ERROR at line 1:
ORA-01821: date format not recognized
My NLS_PARAMETER Setting for timestamp
NLS_TIMESTAMP_FORMAT --> DD-MON-RR HH.MI.SSXFF AM
Thanks,
June 09, 2005 - 6:36 pm UTC
to timestamp
tkyte@ORA9IR2W> select to_date('06/09/2005
2 12:34:45.56','mm/dd/yyyy hh:mi:ss.ff
3 ') from dual;
12:34:45.56','mm/dd/yyyy hh:mi:ss.ff
*
ERROR at line 2:
ORA-01821: date format not recognized
tkyte@ORA9IR2W> 1
1* select to_date('06/09/2005
tkyte@ORA9IR2W> c/date/timestamp
1* select to_timestamp('06/09/2005
tkyte@ORA9IR2W> l
1 select to_timestamp('06/09/2005
2 12:34:45.56','mm/dd/yyyy hh:mi:ss.ff
3* ') from dual
tkyte@ORA9IR2W> /
TO_TIMESTAMP('06/09/200512:34:45.56','MM/DD/YYYYHH:MI:SS.FF')
-----------------------------------------------------------------
09-JUN-05 12.34.45.560000000 PM
That's Great
Maverick, June 09, 2005 - 9:39 pm UTC
Thanks Tom. This I never knew. You are "the Oracle GURU".
Thanks Again.
But pls. clarify me
A reader, June 23, 2005 - 1:24 pm UTC
Hi Tom,
I create a table on the database with the following :
create table test3
(
col1 timestamp with time zone,
col2 timestamp with local time zone
)
insert into test3 values (systimestamp,systimestamp)
I set the following on my TOAD session (client).
alter session set time_zone = 'Asia/Calcutta'
select dbtimezone,sessiontimezone from dual
Dbtime Session Time
-04:00 Asia/Calcutta
I run the following SELECT
select * from test3
Col1 Col2
6/23/2005 1:11:42.095137 PM -04:00 6/23/2005 1:11:42.095137 PM
I am seeing the same values in both columns, only difference being -4.00. My understanding was that Col2 should get the data in the session timezone, but is not happening. Pls. explain why this is happening. I am using 9.2.0.6.0
June 23, 2005 - 7:02 pm UTC
in sqlplus, what do you see.
ops$tkyte@ORA10GR1> create table t
2 (
3 col1 timestamp with time zone,
4 col2 timestamp with local time zone
5 )
6 /
Table created.
ops$tkyte@ORA10GR1> insert into t values (systimestamp,systimestamp);
1 row created.
ops$tkyte@ORA10GR1> select * from t;
COL1
---------------------------------------------------------------------------
COL2
---------------------------------------------------------------------------
23-JUN-05 07.02.24.106003 PM -04:00
24-JUN-05 04.32.24.106003 AM
ops$tkyte@ORA10GR1> select dbtimezone,sessiontimezone from dual;
DBTIME
------
SESSIONTIMEZONE
---------------------------------------------------------------------------
+00:00
Asia/Calcutta
ops$tkyte@ORA10GR1> alter session set time_zone = 'Asia/Calcutta';
Session altered.
ops$tkyte@ORA10GR1> select dbtimezone,sessiontimezone from dual;
DBTIME
------
SESSIONTIMEZONE
---------------------------------------------------------------------------
+00:00
Asia/Calcutta
ops$tkyte@ORA10GR1> select * from t;
COL1
---------------------------------------------------------------------------
COL2
---------------------------------------------------------------------------
23-JUN-05 07.02.24.106003 PM -04:00
24-JUN-05 04.32.24.106003 AM
ops$tkyte@ORA10GR1>
difference between 2 timestamp columns
A reader, November 11, 2005 - 12:02 pm UTC
Hi Tom,
How can I get the difference(in seconds) between 2 timestamp columns located in a table ?
November 12, 2005 - 8:43 am UTC
<quote src=expert oracle database architecture>
Getting the Difference Between Two TIMESTAMPs
This is where the DATE and TIMESTAMP types diverge significantly. Whereas the results of subtracting a DATE from a DATE was a NUMBER, the result of doing the same to a TIMESTAMP is an INTERVAL:
ops$tkyte@ORA10G> select dt2-dt1
2 from (select to_timestamp('29-feb-2000 01:02:03.122000',
3 'dd-mon-yyyy hh24:mi:ss.ff') dt1,
4 to_timestamp('15-mar-2001 11:22:33.000000',
5 'dd-mon-yyyy hh24:mi:ss.ff') dt2
6 from dual )
7 /
DT2-DT1
---------------------------------------------------------------------------
+000000380 10:20:29.878000000
The difference between two TIMESTAMP values is an INTERVAL, and this shows us the number of days and hours/minutes/seconds between the two. If we desire to have the years, months, and so forth, we are back to using a query similar to the one we used with dates:
ops$tkyte@ORA10G> select numtoyminterval
2 (months_between(dt2,dt1),'month')
3 years_months,
4 dt2-add_months(dt1,trunc(months_between(dt2,dt1)))
5 days_hours
6 from (select to_timestamp('29-feb-2000 01:02:03.122000',
7 'dd-mon-yyyy hh24:mi:ss.ff') dt1,
8 to_timestamp('15-mar-2001 11:22:33.000000',
9 'dd-mon-yyyy hh24:mi:ss.ff') dt2
10 from dual )
11 /
YEARS_MONTHS DAYS_HOURS
------------- -----------------------------
+000000001-00 +000000015 10:20:30.000000000
A reader, November 14, 2005 - 9:58 am UTC
Thanks
Confusion with RR Format
Yuan, December 09, 2005 - 8:32 am UTC
The behavior for the RR mask seems to differ between date and timestamp data types:
SQL> select to_date('1-DEC-2005', 'DD-MON-RR HH'), to_timestamp('1-DEC-2005', 'DD-MON-RR HH')
2 from dual;
TO_DATE('
---------
TO_TIMESTAMP('1-DEC-2005','DD-MON-RRHH')
---------------------------------------------------------------------------
01-DEC-05
01-DEC-20 05.00.00.000000000 AM
December 09, 2005 - 12:57 pm UTC
agreed, please contact support with this simple test case.
DBTIMEZONE vs SESSIONTIMEZONE
Russell, January 19, 2006 - 11:08 pm UTC
Hi Tom,
We are using an Oracle 9.2.0.5 database on a HP UNIX 11 box.
The server is physically located in Sydney Australia. I am physically located in Melbourne Australia. Sydney and Melbourne are both in the same timezone (EST + 10:00), and both have daylight saving from end of October to end of March (EDST + 11:00). We telnet into the Unix box, and use SQLPLUS from there.
We have the situation where the majority of data imported into our database is entered in local time to the user (Australia has at least 5 timezones). A small percentage of our data is entered in "Database" time based on the fact that the database where information is gathered from is also in Melbourne.
Last year using information found on Ask Tom, I put together the necessary code, to convert the Database time to local time (whatever that is). The portion of the query I am using at the moment to convert is:
FROM_TZ(CAST( DATECREATED AS TIMESTAMP), DBTIMEZONE) AT TIME ZONE REAL_TZ
This query is converting times from DBTIMEZONE to what the timezone should be (where datecreated, the field being converted, is an Oracle date type, and REAL_TZ is derived from a lookup table, converting from the Application Timezone names to the Internal Oracle timezones. (This bit works ok).
One of our users noticed there were conflicts in timezone calculations from Brisbane (also on the Eastern coast of Australia, but no Daylight Savings, so at the moment they are +10:00).
Issuing the following statement at the moment on my database
SELECT DBTIMEZONE, SESSIONTIMEZONE from dual;
Returns
+10:00 +11:00
1) Shouldn't these two display the same number given the information above?
2) If a database is created with a timezone in Australian EST, will that database always be in Australian EST (hence DBTIMEZONE figure above), even if the database's physical location experiences daylight savings, and the UNIX server's time displayed changes with Daylight saving
3) If q2 above is yes, than should i use SESSIONTIMEZONE in my calculations, to display Daylight savings (or lack thereof in Brisbane)?
4) How would I find out what timezone the database had been created in (or the current timezone name), if for half of the year, both Australia/Brisbane and Australia/Sydney timezones would have shown +10:00, and now the database only shows the numerical offset in SQLPLUS?
Thanks for your time
Russell
January 20, 2006 - 10:10 am UTC
ops$tkyte@ORA10GR2> SELECT DBTIMEZONE, SESSIONTIMEZONE from dual;
DBTIME
------
SESSIONTIMEZONE
---------------------------------------------------------------------------
+00:00
-05:00
No, not in general. My dbtimezone is UTC, my session however is currently in EST (east coast of the US EST)
the client has control over the timezone (sqlplus will pick this up from your environment)
Whether you should use sessiontimezone or dbtimezone is something you'll need to figure out. If the goal is to show what time this event took place according to the wall clock on the end users wall - likely sessiontimezone is correct.
That is your current timezone - +10, unless you change it to one of the "string" names.
Learnt a lot from this thread but stumped converting between timezones in SQL
Tim Onions, June 28, 2006 - 12:17 pm UTC
Having read through this thread I thought I knew enough about timezone conversions but cannot get
FROM_TZ(CAST( DATECREATED AS TIMESTAMP), DBTIMEZONE) AT TIME ZONE v_TZ
to work in SQL - it will work in PL/SQl but errors in SQL statements. Here is my test case:
This works:
select dbtimezone from dual;
DBTIMEZONE
-----------
US/Eastern
declare
l_tz varchar2(64):='America/New_York';
l_tim timestamp;
begin
l_tim:=CAST(to_date('14-nov-2004 18:24:11') AS TIMESTAMP with time zone) AT TIME ZONE l_tz;
dbms_output.put_line(l_tim);
end;
/
But this does not:
select dbtimezone from dual;
DBTIMEZONE
-----------
US/Eastern
declare
l_tz varchar2(24):='America/New_York';
begin
for r in (select FROM_TZ(CAST(to_date('01-Apr-2006 18:24:11') AS TIMESTAMP), DBTIMEZONE ) AT TIME ZONE l_tz c1
from dual) loop
dbms_output.put_line(r.c1);
end loop;
end;
/
declare
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
ORA-06512: at line 4
The error is the same on 9.2.0.7 and 10.2.0.1. I've probably missed something obvious but I've googled and metalunked and foud nothing. Any help you can afford would be appreciated.
June 28, 2006 - 4:55 pm UTC
Looks like SQL does not support a bind variable right there:
ops$tkyte@ORA9IR2> select FROM_TZ(CAST(to_date('01-Apr-2006 18:24:11','dd-mon-yyyy hh24:mi:ss')
2 AS TIMESTAMP), DBTIMEZONE ) AT TIME ZONE 'America/New_York' c1 from dual;
C1
---------------------------------------------------------------------------
01-APR-06 06.24.11.000000 PM AMERICA/NEW_YORK
ops$tkyte@ORA9IR2> variable x varchar2(25)
ops$tkyte@ORA9IR2> exec :x := 'America/New_York';
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select FROM_TZ(CAST(to_date('01-Apr-2006 18:24:11','dd-mon-yyyy hh24:mi:ss')
2 AS TIMESTAMP), DBTIMEZONE ) AT TIME ZONE :x c1 from dual;
AS TIMESTAMP), DBTIMEZONE ) AT TIME ZONE :x c1 from dual
*
ERROR at line 2:
ORA-00923: FROM keyword not found where expected
but it looks like it "should"
http://docs.oracle.com/docs/cd/B19306_01/server.102/b14200/expressions006.htm#SQLRF00401
since "expression" is permitted...
So not allowing expr in SQL is a bug
Tim Onions, June 29, 2006 - 12:28 pm UTC
Looks like I need to file a SR (TAR) then, aw shucks!
How to add a new timezone?
A reader, July 11, 2006 - 6:36 pm UTC
We stored different timezones in a table and passing them to from_tz functioned based on users timezone. One of the users using Africa/Harare timezone and the query is failing with ORA-00904 as it is not available from v$timezone_names.
How to fix this problem? Is it possible to add new timezone info to Oracle ?
July 12, 2006 - 3:28 pm UTC
use 9i and above, full timezone support with the new timestamp with timezone datatypes.
No, you cannot add new timezones for new_tz, from_tz and such (old functions).
But comprehensive timezone support is there in 9i.
Update
A reader, July 11, 2006 - 7:40 pm UTC
This is the correct error message I got for the above problem.
ORA-01882: timezone region not found
Thanks
A reader, July 12, 2006 - 7:13 pm UTC
Thanks for your comments.
We are using 9.2.0.5 and this timezone is not available. It seems that Java and Oracle are following different standards. We are planning to change the code to map the nearest Oracle timezone.
Timezone Time Conflict
Arindam Mukherjee, August 21, 2006 - 3:39 am UTC
Respected Mr. Tom,
First off, I have read
http://asktom.oracle.com/~tkyte/timedate.zip file. In the tables.sql, you did not consider any date type column.
Please Note - I am working for AMD project from India using AMD Database server located in Austin USA. Dbtimezone = -05:00 in the database and I have altered sessiontimezone to -08:00 by alter session. The following are my output.
SQL> ALTER SESSION SET TIME_ZONE = '-5:00';
Session altered.
SQL> SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL;
CURRENT_TIMESTAMP
---------------------------------------------------------------------------
LOCALTIMESTAMP
---------------------------------------------------------------------------
21-AUG-06 02.07.14.315824 AM -05:00
21-AUG-06 02.07.14.315824 AM
1 row selected.
SQL> ALTER SESSION SET TIME_ZONE = '-8:00';
Session altered.
SQL>
SQL> SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL;
CURRENT_TIMESTAMP
---------------------------------------------------------------------------
LOCALTIMESTAMP
---------------------------------------------------------------------------
20-AUG-06 11.08.36.120820 PM -08:00
20-AUG-06 11.08.36.120820 PM
1 row selected.
Dramatically date has been changed from 21 to 20 August.
Now I have created one table as follows,
create table arin_1
(atz timestamp with time zone,
altz timestamp with local time zone,
at timestamp,
ad date
)
/
insert into arin_1 values(sysdate, sysdate,sysdate,sysdate)
/
insert into arin_1 values(systimestamp, systimestamp,systimestamp,sysdate)
/
insert into arin_1 values(localtimestamp, localtimestamp,localtimestamp,sysdate)
/
insert into arin_1 values(CURRENT_TIMESTAMP, CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,sysdate)
/
Now I have created one table as follows,
create table arin_1
(atz timestamp with time zone,
altz timestamp with local time zone,
at timestamp,
ad date
)
/
insert into arin_1 values(sysdate, sysdate,sysdate,sysdate)
/
insert into arin_1 values(systimestamp, systimestamp,systimestamp,sysdate)
/
insert into arin_1 values(localtimestamp, localtimestamp,localtimestamp,sysdate)
/
insert into arin_1 values(CURRENT_TIMESTAMP, CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,sysdate)
/
I could not find any difference except timestamp with time zone datatype where -05:00 has been added to spice up. I executed several times those insert statements, the result seems conflicting. I am not saying its wrong rather I must confess I am not able to understand the usage of these two new timestamp timestamp with time zone and timestamp with local time zone as well. My question stems from Daylight Savings support and then I get trapped in this vicious circle. Now I request you to read Oracle 9i Database Concept chapter 12, the example where San Francisco Database is referred. I have already read another thread "daylight savings" to this site.
Hope I have got across my message to you and you please explain it.
August 27, 2006 - 9:07 am UTC
if you have 9i, you would not use the timezone.zip 8i plsql package to implement rudimentary timezone support - since 9i has it built in.
I don't understand this comment at all:
"Dramatically date has been changed from 21 to 20 August. "
why dramatically? You set your offset from UTC back far enough to become "yesterday"
Here is an excerpt from my book "Expert Oracle Database Architecture" regarding this datatype:
<quote>
TIMESTAMP WITH TIME ZONE Type
The TIMESTAMP WITH TIME ZONE type inherits all of the qualities of the TIMESTAMP type and adds time zone support. The TIMESTAMP WITH TIME ZONE type consumes 13 bytes of storage, with the extra two bytes being used to preserve the time zone information. It differs from a TIMESTAMP structurally only by the addition of these 2 bytes:
ops$tkyte@ORA10G> create table t
2 (
3 ts timestamp,
4 ts_tz timestamp with time zone
5 )
6 /
Table created.
ops$tkyte@ORA10G> insert into t ( ts, ts_tz )
2 values ( systimestamp, systimestamp );
1 row created.
ops$tkyte@ORA10G> select * from t;
TS TS_TZ
---------------------------- -----------------------------------
28-JUN-05 01.45.08.087627 PM 28-JUN-05 01.45.08.087627 PM -04:00
ops$tkyte@ORA10G> select dump(ts), dump(ts_tz) from t;
DUMP(TS)
-------------------------------------------------------------------------------
DUMP(TS_TZ)
-------------------------------------------------------------------------------
Typ=180 Len=11: 120,105,6,28,14,46,9,5,57,20,248
Typ=181 Len=13: 120,105,6,28,18,46,9,5,57,20,248,16,60
So, as you can see, upon retrieval the default TIMESTAMP format included the time zone information (I was on East Coast U.S. time during daylight saving time when this was executed).
TIMESTAMP WITH TIME ZONEs store the data in whatever time zone was specified when the data was stored. The time zone becomes part of the data itself. Note how the TIMESTAMP WITH TIME ZONE field stored
18,46,9
for the hour, minutes, and seconds (in excess-1 notation, so that is 17:45:08), whereas the TIMESTAMP field stored simply
14,46,9
, which is 13:45:09the exact time in the string we inserted. The TIMESTAMP WITH TIME ZONE had four hours added to it, in order to store in GMT (also known as UTC) time. The trailing 2 bytes are used upon retrieval to properly adjust the TIMESTAMP value.
It is not my intention to cover all of the nuances of time zones here in this book; that is a topic well covered elsewhere. To that end, Ill just point out that there is support for time zones in this datatype. This support is more relevant in applications today than ever before. A decade ago, applications were not nearly as global as they are now. In the days before widespread Internet use, applications were many times distributed and decentralized, and the time zone was implicitly based on where the server was located. Today, with large centralized systems being used by people worldwide, the need to track and use time zones is very relevant.
Before time zone support was built into a datatype, it would have been an application function to store the DATE and in another column the time zone information, and then supply functions to convert DATEs from one time zone to another. Now that is the job of the database, and it is able to store data in multiple time zones:
ops$tkyte@ORA10G> create table t
2 ( ts1 timestamp with time zone,
3 ts2 timestamp with time zone
4 )
5 /
Table created.
ops$tkyte@ORA10G> insert into t (ts1, ts2)
2 values ( timestamp'2005-06-05 17:02:32.212 US/Eastern',
3 timestamp'2005-06-05 17:02:32.212 US/Pacific' );
1 row created.
and perform correct TIMESTAMP arithmetic on them:
ops$tkyte@ORA10G> select ts1-ts2 from t;
TS1-TS2
---------------------------------------------------------------------------
-000000000 03:00:00.000000
Since there is a three-hour time difference between those two time zones, even though they show the same time of 17:02:32.212, the interval reported is a three-hour difference. When performing TIMESTAMP arithmetic on TIMESTAMPS WITH TIME ZONE types, Oracle automatically converts both types to UTC time first and then performs the operation.
TIMESTAMP WITH LOCAL TIME ZONE Type
This type works much like the TIMESTAMP column. It is a 7- or 11-byte field (depending on the precision of the TIMESTAMP), but it is normalized to be stored in databases time zone. To see this, well use the DUMP command once again. First we create a table with three columnsa DATE, a TIMESTAMP WITH TIME ZONE, and a TIMESTAMP WITH LOCAL TIME ZONEand then we insert the same value into all three columns:
ops$tkyte@ORA10G> create table t
2 ( dt date,
3 ts1 timestamp with time zone,
4 ts2 timestamp with local time zone
5 )
6 /
Table created.
ops$tkyte@ORA10G> insert into t (dt, ts1, ts2)
2 values ( timestamp'2005-06-05 17:02:32.212 US/Pacific',
3 timestamp'2005-06-05 17:02:32.212 US/Pacific',
4 timestamp'2005-06-05 17:02:32.212 US/Pacific' );
1 row created.
ops$tkyte@ORA10G> select dbtimezone from dual;
DBTIMEZONE
----------
US/Eastern
Now, when we dump those values as follows:
ops$tkyte@ORA10G> select dump(dt), dump(ts1), dump(ts2) from t;
DUMP(DT)
------------------------------------
DUMP(TS1)
------------------------------------
DUMP(TS2)
------------------------------------
Typ=12 Len=7: 120,105,6,5,18,3,33
Typ=181 Len=13: 120,105,6,6, 1,3,33,12,162,221,0,137,156
Typ=231 Len=11: 120,105,6,5,21,3,33,12,162,221,0
we can see that in this case, three totally different date/time representations were stored:
* DT: This column stored the date/time 5-Jun-2005 17:02:32. The time zone and fractional seconds are lost because we used the DATE type. No time zone conversions were performed at all. We stored the exact date/time inserted, but lost the time zone.
* TS1: This column preserved the TIME ZONE information and was normalized to be in UTC with respect to that TIME ZONE. The inserted TIMESTAMP value was in the US/Pacific time zone, which at the time of this writing was seven hours off UTC. Therefore, the stored date/time was 6-Jun-2005 00:02:32.212. It advanced our input time by seven hours to make it UTC time, and it saved the time zone US/Pacific as the last 2 bytes so this data can be properly interpreted later.
* TS2: This column is assumed to be in the databases time zone, which is US/Eastern. Now, 17:02:32 US/Pacific is 20:02:32 US/Eastern, so that is what was stored in the bytes
21,3,33
(excess-1 notation; remember to subtract 1).
Since the TS1 column preserved the original time zone in the last 2 bytes, well see the following upon retrieval:
ops$tkyte@ORA10G> select ts1, ts2 from t;
TS1
----------------------------------------
TS2
----------------------------------------
05-JUN-05 05.02.32.212000 PM US/PACIFIC
05-JUN-05 08.02.32.212000 PM
The database would be able to show that information, but the TS2 column with the LOCAL TIME ZONE (the time zone of the database) shows the time in databases time zone, which is the assumed time zone for that column (and in fact all columns in this database with the LOCAL TIME ZONE). My database was in the US/Eastern time zone, so 17:02:32 US/Pacific on the way in is now displayed as 8:00 pm East Coast time on the way out.
The TIMESTAMP WITH LOCAL TIME ZONE provides sufficient support for most applications, if you need not remember the source time zone, but only need a datatype that provides consistent worldwide handling of date/time types. Additionally, the TIMESTAMP(0) WITH LOCAL TIMEZONE provides you the equivalent of a DATE type with time zone supportit consumes 7 bytes of storage and the ability to have the dates stored normalized in UTC form.
One caveat with regard to the TIMESTAMP WITH LOCAL TIME ZONE type is that once you create tables with this column, you will find your databases time zone is frozen, and you will not be able to change it:
ops$tkyte@ORA10G> alter database set time_zone = 'PST';
alter database set time_zone = 'PST'
*
ERROR at line 1:
ORA-30079: cannot alter database timezone when database has
TIMESTAMP WITH LOCAL TIME ZONE columns
ops$tkyte@ORA10G> !oerr ora 30079
30079, 00000, "cannot alter database timezone when database has
TIMESTAMP WITH LOCAL TIME ZONE columns"
// *Cause: An attempt was made to alter database timezone with
// TIMESTAMP WITH LOCAL TIME ZONE column in the database.
// *Action: Either do not alter database timezone or first drop all the
// TIMESTAMP WITH LOCAL TIME ZONE columns.
It should be obvious why, if you were to change the databases time zone, you would have to rewrite every single table with a TIMESTAMP WITH LOCAL TIME ZONEtheir current values would be wrong given the new time zone!
</quote>
Logically corrupted data
A reader, September 18, 2006 - 2:50 pm UTC
Hi Tom:
Excellent thread.
Is it not possible to logically corrupt the data in the date datatype column if the schema is moved from a d/b in GMT time zone to a d/b in EST(USA) time Zone?
How should we handle this scenario?
Thanks
Ma$e
September 19, 2006 - 2:24 am UTC
no, it would not. the date would be moved as a 7 byte binary field - it would not change.
The date datatype never had a timezone associated with it, so it would not be "corrupt".
if it was 5am on Sept 19th, 2006 when you started...
it'll be 5am on Sept 19th, 2006 when you finish.
If that is not what you want, only you can describe what you really would like to have happen and then we can implement that.
Moving schema across time zones
A reader, September 19, 2006 - 1:37 pm UTC
Here what I had in mind that could lead to logical corruption.
Let's say we have a table with a date column and this existed on a d/b in the UK with the dbtimezone set to GMT.
All data that was entered in that table reflected the date and time according to GMT time zone.
Now when we move that table to a database that is set for GMT-4 (US-EST), is the date data in that table not logically corrupt? All that data is now associated with a database in the EST time zone.
True the original data and the new data never had timezone associated with it.
Unless I was aware (mentally) that this column in this table has data that originated from GMT timezone, the results I get from my sql would be incorrect. Correct?
Thanks for your assistance.
Ma$e
September 19, 2006 - 2:55 pm UTC
it is only logically corrupt if you deem it so - the 7 byte date format has no timezone, it just just a date/time.
Only if you deem it to need an update would it be "logically corrupt" and if so, you know what to do (update it!)
the results from your sql would not be incorrect, unless and until you say they are - in which case, you need to update your data to be in East Coast time.
Brad, November 19, 2006 - 12:26 am UTC
Tom,
We have date data in the following format..This is as it comes from the source system...I need to extract the time portion of it and round it to the
nearest half hour...the sample data is as shown below
CREATE TABLE test
(
START_TIME CHAR(19 BYTE) NOT NULL,
END_TIME CHAR(19 BYTE) NOT NULL
);
Insert into TEST
(START_TIME, END_TIME)
Values
('2006-11-08T15:53:52', '2006-11-08T15:53:53');
Insert into TEST
(START_TIME, END_TIME)
Values
('2006-11-08T16:51:46', '2006-11-08T16:51:56');
Insert into TEST
(START_TIME, END_TIME)
Values
('2006-11-08T16:58:23', '2006-11-08T16:58:25');
Insert into TEST
(START_TIME, END_TIME)
Values
('2006-11-08T07:05:01', '2006-11-08T07:05:01');
Insert into TEST
(START_TIME, END_TIME)
Values
('2006-11-08T07:05:01', '2006-11-08T07:05:01');
Insert into TEST
(START_TIME, END_TIME)
Values
('2006-11-08T07:05:01', '2006-11-08T07:05:01');
Insert into TEST
(START_TIME, END_TIME)
Values
('2006-11-08T07:05:01', '2006-11-08T07:05:01');
Insert into TEST
(START_TIME, END_TIME)
Values
('2006-11-10T16:05:13', '2006-11-10T16:05:52');
Insert into TEST
(START_TIME, END_TIME)
Values
('2006-11-10T16:18:34', '2006-11-10T16:18:35');
Insert into TEST
(START_TIME, END_TIME)
Values
('2006-11-10T16:11:35', '2006-11-10T16:12:05');
COMMIT;
But i need to round the data to the nearest half hour...like 1320 should be rounded to 1330 and 1310 should be rounded to 1300 ( 0-15 should be rounded to 0 and
15-30 rounded to 30..30-45 should be rounded tp 30 and 45-60 should be rounded to the next hour...)
basically i need the output data to be like this (0,30,100,130,200,230,...2330) as numbers?
select
to_char(to_date(substr(start_time,12),'hh24:mi:ss'),'hh24mi') from
test..
i got this far,....any suggestions?
November 20, 2006 - 2:43 am UTC
I have no idea why
a) you would use char, stop using that type, it is a "bad" type.
b) you would use a string to store a date.
how can
0-15 round to 0
15-30 round to 30
same with 45?
Timestamp with timezone not populated correctly
Amit, January 28, 2007 - 11:27 pm UTC
Hi,
I have the following timestamp with timezone issue.
From java program I am passing the datetime with timezone value to database (INTERVALTIME (column name)).
PrepareStatemenet statement;
..
...
java.util.Date dt = ISODateUtil.parse("2006-06-21T18:00:00+00:00");
SimpleTimeZone zone = new SimpleTimeZone(0,"IST");
Calendar cal = converting the dt to calendar;
cal.setTimeZone(zone);
statement.setTimestamp(col, new Timestamp(cal.getTimeInMillis()),cal);
In database INTERVALTIME (column name) coulmn type is TiMESTAMP WITH TIMEZONE.Database is in PDT (GMT-8) timezone.
Now the problem is :
Even if i send the datetime of IST timezone, it is displaying in PDT (GMT-8) timezone.Any clues, suggestions will be highly appriciated. In Database we are seeing date as
2006-12-02T15:00:00.000-08:00
but we are expecting "2006-12-02T15:00:00.000-00:00"
How can i resolve this issue? Tom please suggest as this is an urgent issue.
How to get timezone region from database time zone.
Shivdeep Modi, February 12, 2007 - 5:50 am UTC
Hi Tom,
How to get timezone region from database time zone?
Value should be one of the following :
select distinct(TZABBREV) from V$TIMEZONE_NAMES;
Regards,
Shivdeep Modi
February 12, 2007 - 10:37 am UTC
depends on how the database was setup, not all databases have one - some just have offsets from UTC - see dbtimezone.
how to calculate break fast,lunch and dinner time between two times
AM, April 03, 2007 - 7:00 am UTC
Hi
i have this type of data in my table
OT_DATE FROM_TIME TO_TIME FROM_ TO_TIME
10-JAN-07 01-APR-07 01-APR-07 17:30 19:30
11-JAN-07 01-APR-07 01-APR-07 17:30 19:30
12-JAN-07 01-APR-07 01-APR-07 18:30 20:30
13-JAN-07 01-APR-07 01-APR-07 08:00 17:30
14-JAN-07 01-APR-07 01-APR-07 08:00 17:30
15-JAN-07 01-APR-07 01-APR-07 17:30 19:30
I need to calculate 3 more columns breakfast ,lunch and dinner.The rule is from 07:00 to 08:00 one breakfast will be counted.from 13:00 to 14:00 one lunch will be counted
and from 20:00 to 00:00(12pm) one dinner will be counted
Against each row i need to calculate number of bfast,lunch and dinner if from_time and to_time contains any of time slots alloted for each meal then return 1 for respective column else return 0 for that column.Is it possible through sql.
Regards
April 04, 2007 - 9:44 am UTC
it is likely possible.
but we won't consider looking at it without an easy to enter example.
no create table
no insert into
no look
and a better (much better) explanation of what is represented by the data that exists - it doesn't compute. I cannot make sense of the data displayed.
eXPLANATION
A reader, April 04, 2007 - 11:49 am UTC
Sorry for not being meaningful
create table OVERTIME(
ftime VARCHAR2(5),
to_time VARCHAR2(5));
INSERT INTO OVERTIME VALUES('17:30','19:30');
INSERT INTO OVERTIME VALUES('08:00','17:30');
INSERT INTO OVERTIME VALUES('17:30','19:30')
INSERT INTO OVERTIME VALUES('06:00','10:30')
INSERT INTO OVERTIME VALUES('11:30','12:30')
April 04, 2007 - 12:06 pm UTC
that is even less meaningful?
since it added no text to explain what you meant
and it doesn't even match the first table.
Review
A reader, April 04, 2007 - 1:36 pm UTC
considering my last tabe i.e overtime,i have a table in which from_time and to_time is entered
I need to calculate 3 more columns breakfast ,lunch and dinner.The rule is from 07:00 to 08:00 one breakfast will be counted.from 13:00 to 14:00 one lunch will be counted
and from 20:00 to 00:00(12pm) one dinner will be counted
Now as the data is entered according to hours worked(from hours to to_hours),the duration may contain time slots aloted for different meals.
Against each row i need to calculate number of bfast,lunch and dinner if from_time and to_time contains any of time slots alloted for each meal then return 1 for respective column else return 0 for that column.
say if from_time is 0700 and to_time is 1500 it contain one time slot for breakfast i.e 07:00 to 08:00 and one for lunch i.e 13:00 to 14:00 so i need to display 1 in breakfast and 1 in lunch and 0 in dinner column.
Thanks for your patience
Regards
April 04, 2007 - 3:43 pm UTC
answer Michel's question below, since I would obviously have the same question.
What happens with the partial overlaps.
To Atif (A reader)
Michel Cadot, April 04, 2007 - 2:45 pm UTC
You still don't answer to my question on OraFAQ forum:
If from_time is 7:30 and to_time is 13:30, what have we to count? A breakfast, a lunch, nothing?
Regards
Michel
Answer
Atif, April 05, 2007 - 7:58 am UTC
If from_time is 7:30 and to_time is 13:30 then we will have one break fast, one lunch and zero dinner.
because 7:30 lies between 07:00 to 08:00(time slot for bfast) and 13:30 lies between 13:00 to 14:00(time slot for lunch).
Regards
April 05, 2007 - 11:12 am UTC
ops$tkyte%ORA10GR2> select ftime, to_time,
2 case when to_date( ftime,'hh24:mi') <= to_date( '08:00', 'hh24:mi' ) and to_date(to_time,'hh24:mi') >= to_date( '07:00', 'hh24:mi' )
3 then 'Y'
4 else 'N'
5 end breakfast,
6 case when to_date( ftime,'hh24:mi') <= to_date( '14:00', 'hh24:mi' ) and to_date(to_time,'hh24:mi') >= to_date( '13:00', 'hh24:mi' )
7 then 'Y'
8 else 'N'
9 end lunch,
10 case when to_date( ftime,'hh24:mi') <= to_date( '23:59:59', 'hh24:mi:ss' ) and to_date(to_time,'hh24:mi') >= to_date( '20:00', 'hh24:mi' )
11 then 'Y'
12 else 'N'
13 end dinner
14 from overtime
15 /
FTIME TO_TI B L D
----- ----- - - -
17:30 19:30 N N N
08:00 17:30 Y Y N
17:30 19:30 N N N
06:00 10:30 Y N N
11:30 12:30 N N N
11:30 23:30 N Y Y
6 rows selected.
To share the answer I gave in OraFAQ
Michel Cadot, April 05, 2007 - 12:02 pm UTC
(The test case given by Atif was not the same one, so results are different.)
<quote>
I choose that at least one minute should be in the period to take the meal into account.
If you don't agree then change the boundaries.
SQL> select o_date, ftime, to_time,
2 case when ftime between '0700' and '0759'
3 or to_time between '0701' and '0800'
4 or ( ftime < '0700' and to_time > '0800' )
5 then 'Y'
6 else 'N'
7 end breakfast,
8 case when ftime between '1300' and '1359'
9 or to_time between '1301' and '1400'
10 or ( ftime < '1300' and to_time > '1400' )
11 then 'Y'
12 else 'N'
13 end lunch,
14 case when ftime between '2000' and '2359'
15 or to_time between '2001' and '2359'
16 or to_time = '0000'
17 then 'Y'
18 else 'N'
19 end dinner
20 from overtime
21 order by 1, 2
22 /
O_DATE FTIME TO_TI B L D
----------- ----- ----- - - -
01-JAN-2007 0730 1400 Y Y N
02-JAN-2007 0700 1200 Y N N
03-JAN-2007 1110 1525 N Y N
04-JAN-2007 1720 2300 N N Y
05-JAN-2007 0800 2300 N Y Y
5 rows selected.
</quote>
Regards
Michel
Diffrence between 2 timestamps in minutes
A reader, July 31, 2007 - 3:11 pm UTC
Tom,
I have the following 2 tables
***********************
create table A (taskid number, task_start_timestamp varchar2(30),TASK_END_TIMESTAMP varchar2(30));
create table B (taskid number, duration number(9,2));
************************
Table A stores the timestamp in a varchar2 datatype (I have to live with it, cannot change the datatype), a new task is inserted into A with timestamp in start column and upon completion the end column is populated. Now i need to calculte the diffrence between the end and start time in minutes and insert it into table B under duration.
Here is some sample data:
******************
insert into a values (1,'2007-07-08 21:48:10.264','2007-07-08 21:48:10.264');
insert into a values (2,'2007-07-08 21:48:10.264','2007-07-08 22:30:25.683');
insert into a values (3,'2007-07-08 22:01:58.749','2007-07-08 22:42:55.489');
commit;
********************
Here is the query that I am currently using to get the value in minutes.
********
select
trunc((extract (day from (to_timestamp(TASK_END_TIMESTAMP,'yyyy-mm-dd HH24:MI:SS.FF')-to_timestamp(task_start_timestamp,'yyyy-mm-dd HH24:MI:SS.FF')))*24*60)+
(extract (hour from (to_timestamp(TASK_END_TIMESTAMP,'yyyy-mm-dd HH24:MI:SS.FF')-to_timestamp(task_start_timestamp,'yyyy-mm-dd HH24:MI:SS.FF')))*60)+
extract (minute from (to_timestamp(TASK_END_TIMESTAMP,'yyyy-mm-dd HH24:MI:SS.FF')-to_timestamp(task_start_timestamp,'yyyy-mm-dd HH24:MI:SS.FF')))+
(extract (second from (to_timestamp(TASK_END_TIMESTAMP,'yyyy-mm-dd HH24:MI:SS.FF')-to_timestamp(task_start_timestamp,'yyyy-mm-dd HH24:MI:SS.FF')))/60),2)
FROM
A
******
Is there any other simple and elegant way to do it?
August 02, 2007 - 10:38 am UTC
holy cow, what is up with your datatype selection. why do people do this, why why why. I'll never, as long as I live, understand this.
ops$tkyte%ORA10GR2> select (to_date( substr( task_end_timestamp,1,19), 'yyyy-mm-dd hh24:mi:ss')
2 -to_date( substr( task_start_timestamp,1,19), 'yyyy-mm-dd hh24:mi:ss'))*24*60 mins
3 from a
4 /
MINS
----------
0
42.25
40.95
wether daylight savings was applicable on the day
umesh_kasturi, September 25, 2007 - 2:44 am UTC
Tom
My servers are in U.S of A. The creation_date column contains the date and time created of the user
Can I know wether on any given date , wether daylight savings was present ?
September 26, 2007 - 9:13 pm UTC
whether....
it entirely depends on whether the server itself was setup with timezones...
mine for example is running GMT...
now, you could twiddle around a bit with timestamp functions -but what does
select dbtimezone from dual
return and are you SURE it'll be that way forever?
whether daylight present
umesh_kasturi, September 27, 2007 - 5:08 am UTC
SQL> select dbtimezone from dual ;
DBTIME
------
+01:00
This remains the same.
If I simplify the question I think I am looking for :
For any given year which are the 2 dates , between which the daylight savings is presnt ( in a PST zone)
September 27, 2007 - 7:19 am UTC
your database is in a fixed timezone - it is not in a regional one - so daylight savings is not used.
your answer is simple: it is never daylight savings. Many regions do not observe it - your database is +1 from GMT.
time zone
umesh_kasturi, September 27, 2007 - 7:31 am UTC
Sorry I was not clear The problem for me is I store dates in the DB in PST. I access them to service customers in India. What I want is to get the date on which the day light saving is used and accordingly do the logic in Java code to add or subtract the hours required
Hope I am clear this time
September 28, 2007 - 3:09 pm UTC
well, do you always store them in PST or do you store them in Pacific time - meaning, some are PST and some are PDT.
try these methods
Pasko, September 27, 2007 - 11:10 am UTC
Hi Umesh,
try one of these methods to convert the dates between
different time zones.
Ask your DBA to load the correct Time-zone files if some
time zone names are missing.
to see all time zones use:
select * from V$TIMEZONE_NAMES
select NEW_TIME (
to_date ('1999-12-01 11:00:00', 'YYYY-MM-DD HH:MI:SS'),
'EST',
'PST'
) "West Coast Time method_1",
FROM_TZ (
cast (to_date ('1999-12-01 11:00:00', 'YYYY-MM-DD HH:MI:SS') as timestamp),
'America/New_York'
) at time zone 'America/Los_Angeles' "West Coast Time method_2"
from dual
September 28, 2007 - 4:07 pm UTC
best to find out if they really meant what they said - that the time is stored in a canonical format - using PST.
I'd be really surprised if the answer is "yes", rather it is stored in PST and PDT - and without a timezone attribute, it is pointless to try and adjust some of the times - because you simply do not know what timezone they were in during the overlap period.
Getting dat acomponent from TIMESTAMP
Bob, September 28, 2007 - 12:39 pm UTC
Hi Tom,
How do I get the date component from a TIMESTAMP for use in a where clause. When I do
select a,
b
from c
where trunc(cast(a.x as date)) >= '13-AUG-2007'
and trunc(cast(a.y as date)) <= '04-SEP-2007'
I get misleading results (i.e more records) if I switch
'04-SEP-2007' to '03-SEP-2007'.
Any ideas to get round this would be appreciated. Thanks
September 28, 2007 - 6:05 pm UTC
why are you comparing a date to a string?
anyway, no create table, no inserts, no look
comparing TIMESTAMP
Bob, September 28, 2007 - 6:33 pm UTC
Hi Tom - sorry for being ambiguity. Here are the details:
desc T_JACSanctions
Name Null? Type
----------------------------------------- -------- -------
SANCTIONSREF NOT NULL NUMBER
PAYMENTREF NOT NULL NUMBER
ORIGINATINGSITEREF NOT NULL NUMBER
SITEREF NOT NULL NUMBER
SANCTIONDATE NOT NULL TIMESTAMP(6)
TOTALTOPRIME NUMBER
TOTALFROMPRIME NUMBER
POSSIBLEVIOLATION NUMBER
EAPBLOCKED NUMBER
EAPREJECTED NUMBER
EAPPENDED NUMBER
EAPWAIVED NUMBER
EAPINSPECT NUMBER
EAPARCHIVED NUMBER
EAPINSPECTED NUMBER
EAPRETURNED NUMBER
EAPREFERRED NUMBER
EAPOFACSENT NUMBER
CREATEDBY NOT NULL NUMBER
SANCTIONEDREASON VARCHAR2(4000)
USERCOMMENT VARCHAR2(4000)
We insert into the table via a procedure that is called from Java. Thus:
CREATE OR REPLACE PROCEDURE p_jacpopulatesanctions (ipaymentstatus IN NUMBER,
ipossibleviolation IN NUMBER,
itotaltoprime IN number,
itotalfromprime IN NUMBER,
ipaymentref IN NUMBER,
iorigsiteref IN NUMBER,
icurrsiteref IN NUMBER,
iuserref IN NUMBER,
ireason IN VARCHAR2,
icomment IN VARCHAR2,
ostatus OUT NUMBER)
IS
payment_status_not_found EXCEPTION;
BEGIN
SAVEPOINT sanctions_start;
IF ipaymentstatus NOT IN(13,15,16,17,18,19,20,21,22,23) THEN
RAISE payment_status_not_found;
END IF;
INSERT INTO t_jacsanctions
VALUES
(s_jacsanctionsref.NEXTVAL,
ipaymentref,
iorigsiteref,
icurrsiteref,
sysdate,
nvl(itotaltoprime,0),
nvl(itotalfromprime,0),
NVL(ipossibleviolation,0),
DECODE(ipaymentstatus,16,1,0) , --BLOCKED
DECODE(ipaymentstatus,17,1,0) , --REJECTED
DECODE(ipaymentstatus,18,1,0) , --PENDED
DECODE(ipaymentstatus,20,1,0) , --WAIVED
DECODE(ipaymentstatus,13,1,0) , --INSPECT
DECODE(ipaymentstatus,19,1,0) , --ARCHIVED
DECODE(ipaymentstatus,15,1,0) , --INSPECTED
DECODE(ipaymentstatus,22,1,0) , --RETURNED
DECODE(ipaymentstatus,21,1,0) , --REFERRED
DECODE(ipaymentstatus,23,1,0) , --OFACSENT
iuserref,
ireason,
icomment
);
ostatus := 0;
EXCEPTION
WHEN payment_status_not_found THEN
ROLLBACK TO sanctions_start;
ostatus := -1;
WHEN OTHERS THEN
ROLLBACK TO sanctions_start;
ostatus := -2;
END;
/
We are reporting from this table using the following SQL query:
SELECT
rowid,
createdby,
sanctionsref,
b.sanctiondate,
DECODE(NVL(b.possibleviolation,0), -1, 0, NVL(b.possibleviolation,0) ) viol,
eapinspect,
eapreferred,
rn
FROM (SELECT b.*,
ROW_NUMBER() OVER
(PARTITION BY b.paymentref
ORDER BY sanctiondate DESC) rn
FROM t_jacsanctions b,
t_jacpayment c
WHERE c.paymentref = b.paymentref
and to_timestamp(b.sanctiondate) between timestamp '2007-08-13 00:00:00' and timestamp'2007-09-04 00:00:00'
AND b.createdby IN(2585,2788,2430,2208,2497,2584,2496,2713,2789)
) b
WHERE RN < 2
The date range predicate misses out a record dated:
14th August 2007 5:48pm. This record is within the range of the predicate. My question is why and what am I doing wrong? Thanks very much Tom.
and to_char(cast(b.sanctiondate as date), 'YYYYMMDD') = '20070814'
October 03, 2007 - 1:29 pm UTC
I cannot being to tell you how much I DESPISE your exception block.
the rollback to savepoint - would not be necessary if you let the exception propagate.
the use of "return codes" is so 1980 - I hate when others, I really wish it did not exist.
the 21st century language you are invoking this from - Java - it does this exception stuff, plsql does it, most real languages do it - use it the right way. I hope your java isn't filled with try catch blocks that catch any exception and hide them too.
(I won't even comment on the usefullness of defining and catching your own exception like that, lets just use a goto in the future, it would be more obvious what you are trying to do!!!)
and your insert - you are missing the list of columns...
if you use a timestamp column in the table, why would not you insert systimestamp? instead of sysdate.
In any case - since August 14th happened months ago - I obviously do NOT have your data.
How about this:
create table ( just the column(s) needed )
insert into table ( the precise values to reproduce your issue )
select * from t where <condition that doesn't return what you think it should>
try to_timestamp instead of Timestamp '...'
A reader, September 30, 2007 - 5:00 am UTC
Hi Bob,
i have also seen this Timestamp '...' format that you have used in Oracle Docs, but i prefer to use the Function to_timestamp explicitly.
@Tom, is this Timestamp '...' format missing the HH24:MI:SS section?
eg:
SQL> create table t(sanctiondate timestamp(6) ) ;
Tabelle wurde erstellt.
SQL>
SQL> insert into t values(systimestamp);
1 Zeile wurde erstellt.
SQL> /
1 Zeile wurde erstellt.
SQL> /
1 Zeile wurde erstellt.
SQL> commit ;
Transaktion mit COMMIT abgeschlossen.
SQL> select * from t;
SANCTIONDATE
---------------------------------------------------------------------------
30.09.07 10:44:59,078000
30.09.07 10:45:01,593000
30.09.07 10:45:03,156000
SQL> select count(*) from t
where sanctiondate between
to_timestamp('2007-09-01 00:00:00','YYYY-MM-DD HH24:MI:SS')
and to_timestamp('2007-09-30 23:59:59','YYYY-MM-DD HH24:MI:SS');
COUNT(*)
----------
3
TIMESTAMP in a WHERE clause
Bob, September 30, 2007 - 2:56 pm UTC
Hiya - I have tried using to_timestamp with the format mask:
'DD-MON-YYYY HH24:MI:SS.FF'
But it doesn't make any difference I still get that particular record (stated in my last post missing). I looked on Oracle Metalink and they have a workaround:
https://metalink.oracle.com/metalink/plsql/f?p=130:14:11583055226743621763::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,3116276.8,1,1,1,helvetica But that doesn't work for me either - I am a bit stumped!!
TIMESTAMP in WHERE clause
Bob, September 30, 2007 - 3:49 pm UTC
BTW - should have mentioned from the outset, I am on Oracle 9.2.0.7.0. I think they have fix for this problem in Oracle 11 using TRUNC with TIMESTAMP.
Cheers,
Bob
TIMESTAMP in WHERE clause
Bob, September 30, 2007 - 3:49 pm UTC
BTW - should have mentioned from the outset, I am on Oracle 9.2.0.7.0. I think they have fix for this problem in Oracle 11 using TRUNC with TIMESTAMP.
Cheers,
Bob
why do you need trunc?
Pasko, October 01, 2007 - 5:56 am UTC
Hi Bob,
in your query i see:
...
...and to_timestamp(b.sanctiondate) between timestamp '2007-08-13 00:00:00' and
timestamp'2007-09-04 00:00:00'
why do you need a trunc or to_timestamp on b.sanctiondate?
i think it's not a good idea to put a function on a DB Column on the left hand side of a where condition because this will prevent the use of an index on b.sanctiondate,
unless if you have a function-based index on that ;)
have you tried:
...and b.sanctiondate between
to_timestamp('2007-08-13 00:00:00','YYYY-MM-DD HH24:MI:SS') and
to_timestamp('2007-09-04 00:00:00','YYYY-MM-DD HH24:MI:SS')
Hope this helps.
TIMESTAMP and Where clause
Bob, October 01, 2007 - 9:26 am UTC
Hi Pasko - thanks for your advice. Yes, I have tried doing that, but no joy.
BTW, you are absolutely spot on with disabling my index with a function and FBI's.
I think in Oracle 9.2 we are fairly limited by how we can manipulate a TIMESTAMP in a where clause. I had to revert back to modifying the column as a DATE. I would be very interested to know if there is a FIX for this.
Cheers,
Bob
TIMESTAMP AND TRUNC
Bob, October 07, 2007 - 5:08 pm UTC
Hi Tom,
Thanks for your comments - I am afraid I come from a IBM Mainframe/COBOL background - hence the status codes which get fed to the Java. And on the use of GOTOS's, they can be used intelligently in PARA's in COBOL!
There was a bug in the code. The situation I had was if there were two payments:
e.g dated 14 Aug 2007
03 Sep 2007
I should pick up 03 Sep 2007. But when the start and end date condition are inside the inline view it picks up the 14 Aug 2007 (I shouldn't see this). So the condition had to be outside the inline view to work...
weird result with daylight saving
A reader, February 20, 2008 - 10:35 am UTC
Tom,
We are running 10.2.0.3.0 release of Oracle.
I run into this weird problem when i try to go over a "Daylight Saving" day..
---
If I try to select a non-existant hour in march(when it jumps from 1:00AM to 3:00 AM), I get correct results:
SQL> select to_timestamp_tz('03/11/2007 02:13:23 US/Central', 'mm/dd/yyyy hh24:mi:ss tzr') from dual;
ORA-01878: specified field not found in datetime or interval
But when I try to do the same by adding an interval to 1 AM, it lets me do that :(
SQL> select to_timestamp_tz('03/11/2007 01:00:00 US/Central', 'mm/dd/yyyy hh24:mi:ss tzr')
2 + to_dsinterval ('0 01:13:23')
3 from dual
4 /
TO_TIMESTAMP_TZ('03/11/200701:
-----------------------------------------------------------
11-MAR-07 02.13.23.000000000 AM US/CENTRAL
What am I doing wrong? Please help!
Thanks a lot
February 20, 2008 - 12:39 pm UTC
that might be an issue, I ran in 11g
1* select to_timestamp_tz('03/11/2007 01:00:00 US/Central', 'mm/dd/yyyy hh24:mi:ss tzr')+ to_dsinterval ('0 01:13:23') from dual
ops$tkyte%ORA11GR1> /
TO_TIMESTAMP_TZ('03/11/200701:00:00US/CENTRAL','MM/DD/YYYYHH24:MI:SSTZR')+T
---------------------------------------------------------------------------
11-MAR-07 03.13.23.000000000 AM US/CENTRAL
please utilize support.
timestamp
A reader, February 26, 2008 - 2:41 pm UTC
Tom:
How do you decide between column type of "date" or "timestamp".
I need to store a server heartbeat (Date/time) the pulse/message is sent from that server using http.
would you use just "date" type? when do you use that timestamp type.
thanks
February 26, 2008 - 3:16 pm UTC
do you need precision beyond second? Yes = timestamp
do you need timezone support? Yes = timestamp
if you get here, use whichever one makes you feel better, a timestamp(0) (no precision after seconds) is just like a date. Timestamps have different functions for them than dates, but if you like dates - no reason not to use them at this point.
at time zone query bind variable
A reader, March 20, 2009 - 12:18 pm UTC
tom,
i am also facing the bind variable issue discussed above
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5011677391274#67042255195840 the query SELECT SYSTIMESTAMP AT TIME ZONE :tz NYTIME FROM DUAL does not work
any option to get it work ?
can i use the following as an alternative..any issues with this approach
SELECT SYSTIMESTAMP AT TIME ZONE TO_CHAR(:TZ) NYTIME FROM DUAL
SELECT SYSTIMESTAMP AT TIME ZONE (SELECT :TZ FROM DUAL) NYTIME FROM DUAL
DB Version-Oracle Database 10g Release 10.2.0.1.0
thanks
jyo
timedate_pkg
A reader, April 21, 2009 - 7:34 am UTC
Hi,
I'm trying to use timestamp_pkg in order to convert between local time and GMT.
Thereby I noticed that the packaged is based on the assumption that you are running with nls_territory = AMERICA where sunday is week day no 1.
So if you (like me) don't use that setting, you'll have to compensate for that. Where I am (Denmark), day no 1 of the week is monday.
/Michael
A reader, November 16, 2009 - 7:14 am UTC
If we want to show user of his local time then we should use timestamp with local time zone?
we have a web application how we get time zone of user from OS? The insertion process will be to get time zone from user OS and set session parameter and simply insert.
On retrieval what will be done for each user. for example user inserted record with +1 time zone and then a user comes with +3 time zone how we show his time.
CREATE TABLE SCOTT.DATE_TABLE
(
TIME_STAMP_TZ TIMESTAMP(6) WITH TIME ZONE,
TIME_STAMP_LTZ TIMESTAMP(6) WITH LOCAL TIME ZONE
);
Insert into DATE_TABLE
(TIME_STAMP_TZ, TIME_STAMP_LTZ)
Values
(TO_TIMESTAMP_TZ('11/16/2009 12:06:25.156597 PM +01:00','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM TZH:TZM'), TO_TIMESTAMP_TZ('11/16/2009 4:06:25.156597 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
COMMIT;
November 23, 2009 - 9:58 am UTC
... we have a web application how we get time zone of user from OS? ...
good question, you would absolutely not get it from ... the database. So, I guess you would have to ask the middle tier developers how to do this.
you would set your sessions timezone and select.
ops$tkyte%ORA11GR2> create table t
2 ( x timestamp,
3 y timestamp with time zone,
4 z timestamp with local time zone
5 )
6 /
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert into t values ( systimestamp, systimestamp, systimestamp );
1 row created.
ops$tkyte%ORA11GR2> select dbtimezone, sessiontimezone, t.* from t;
DBTIME
------
SESSIONTIMEZONE
---------------------------------------------------------------------------
X
---------------------------------------------------------------------------
Y
---------------------------------------------------------------------------
Z
---------------------------------------------------------------------------
+00:00
-05:00
23-NOV-09 10.56.42.020873 AM
23-NOV-09 10.56.42.020873 AM -05:00
23-NOV-09 10.56.42.020873 AM
ops$tkyte%ORA11GR2> alter session set time_zone = '-08:00';
Session altered.
ops$tkyte%ORA11GR2> select dbtimezone, sessiontimezone, t.* from t;
DBTIME
------
SESSIONTIMEZONE
---------------------------------------------------------------------------
X
---------------------------------------------------------------------------
Y
---------------------------------------------------------------------------
Z
---------------------------------------------------------------------------
+00:00
-08:00
23-NOV-09 10.56.42.020873 AM
23-NOV-09 10.56.42.020873 AM -05:00
23-NOV-09 07.56.42.020873 AM
the first one, X is just a timestamp, no timezone information at all.
The second one is of a fixed timzone, it'll be GMT-5 - you can convert it to another timezone, but it'll come back by default as GMT-5
The third one will look at the timezone of the session and adjust the value as appropriate.
A reader, November 24, 2009 - 3:32 am UTC
In my web application user access the application from any where in the world. application server and data base server on same location (NJ). how can i store the time zone in my DB and show the data to user according to his time zone.I am using spring in my middle tier and connection object is not availabel. Application server manges the connection pooling. How can I set the D.B session time zone according to client. OR I have to always send the date with time zone from front end and not use systimstamp
November 24, 2009 - 11:18 am UTC
.. how can i
store the time zone in my DB and show the data to user according to his time
zone. ..
you are the developer - you tell me?
You have to tell the database what timezone you would like to have data displayed in (not the other way around) and if your application framework doesn't permit you to issue an alter session or call a stored procedure to do so - there is nothing I can do to help you, your framework isn't a framework as much as a barrier to being productive.
You would have to figure out what timezone your client would like to see (how you do that is entirely and utterly up to you, yahoo for example has me set my timezone as a preference and they use that). Then you would issue the alter session and data would magically appear in that timezone (if you use the timezone type demonstrated above)
Time Zone lost when TRUNC( )
A reader, February 04, 2011 - 4:56 am UTC
Hi Tom,
how can I preserve the time zone info after TRUNC( ) time column to HH24/DD/WI ?
When I do TRUNC(TSZ,'HH24') I am getting 01-01-2011 10:30:00
I am want to get : 01-01-2011 10:00:00 +5:30
Is there is way to preserve the TZ after TRUNC?
Basicaly we want know from which time zone this data belongs even after TRUNC.
alter session set NLS_TIMESTAMP_TZ_FORMAT='DD-MM-YYYY HH24:MI:SS TZd';
drop table jetload.TestTZ;
CREATE TABLE JETLOAD.TESTTZ (COUNTRY VARCHAR(10), tsz TIMESTAMP WITH TIME ZONE );
INSERT INTO JETLOAD.TESTTZ VALUES('INDIA', TIMESTAMP' 2011-01-01 10:30:00 +5:30');
SELECT COUNTRY, TSZ FROM JETLOAD.TESTTZ;
SELECT COUNTRY, TRUNC(TSZ,'HH24') FROM JETLOAD.TESTTZ;
alter session set succeeded.
drop table jetload.TestTZ succeeded.
CREATE TABLE succeeded.
1 rows inserted
COUNTRY TSZ
---------- -------------
INDIA 01-01-2011 10:30:00 +05:30
COUNTRY TRUNC(TSZ,'HH24')
---------- -------------------------
INDIA 01-01-2011 10:00:00
February 04, 2011 - 9:59 am UTC
trunc takes either a DATE or a NUMBER, in this case your timestamp is implicitly cast as a date (no TZ).
try this approach
ops$tkyte%ORA11GR2> CREATE TABLE TESTTZ (COUNTRY VARCHAR(10), tsz TIMESTAMP WITH TIME ZONE );
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> INSERT INTO TESTTZ VALUES('INDIA', TIMESTAMP' 2011-01-01 10:30:00 +5:30');
1 row created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> SELECT COUNTRY, TSZ,
2 to_timestamp_tz( to_char( tsz, 'yyyymmddhh tzh:tzm'), 'yyyymmddhh tzh:tzm') FROM TESTTZ;
COUNTRY
----------
TSZ
---------------------------------------------------------------------------
TO_TIMESTAMP_TZ(TO_CHAR(TSZ,'YYYYMMDDHHTZH:TZM'),'YYYYMMDDHHTZH:TZM')
---------------------------------------------------------------------------
INDIA
01-JAN-11 10.30.00.000000 AM +05:30
01-JAN-11 10.00.00.000000000 AM +05:30
Time Zone lost when TRUNC( )
A reader, February 08, 2011 - 2:39 am UTC
Hi Tom,
i made small plsql function as given below.
CREATE OR REPLACE FUNCTION JETLOAD.TZ_TRUNC ( tsz TIMESTAMP WITH TIME ZONE, DATE_TRUNC_FROMAT IN VARCHAR2
) RETURN TIMESTAMP WITH TIME ZONE AS
BEGIN
RETURN TO_TIMESTAMP_TZ( TRUNC(TSZ,DATE_TRUNC_FROMAT)||TO_CHAR( TSZ, ' tzh:tzm'), 'DD-MM-YYYY HH24:MI:SS tzh:tzm');
END TZ_TRUNC;
we are using TZ_TRUNC() to create Hr/day/week summery tables , this function is called for millions of rows.
i like to know what is the best way to get good performance.
February 09, 2011 - 7:21 am UTC
get rid of the function and just use the to_* functions directly. If you want to "hide it", use a view (10g or before) or virtual column (11g and above)
Partition on TIMESTAMP WITH TIME ZONE
A reader, February 15, 2011 - 12:39 am UTC
Hi Tom,
I try to create PARTITION on "TIMESTAMP WITH TIME ZONE" column its not supported.
create table OMC.TESTTZ
(COUNTRY VARCHAR(10),
TZ TIMESTAMP WITH TIME ZONE
)
partition by RANGE (TZ)
(
PARTITION DEC_2008 VALUES LESS THAN (TO_TIMESTAMP_TZ('2009-01-01
11:00:00 -8:00','YYYY-MM-DD HH:MI:SS TZH:TZM'))
)
SQL Error: ORA-03001: unimplemented feature
03001. 00000 - "unimplemented feature"
So I created virtual column Timestamp as below.
drop table OMC.TESTTZ;
create table OMC.TESTTZ
(COUNTRY VARCHAR(10),
TZ TIMESTAMP WITH TIME ZONE,
ts TIMESTAMP generated always as ( to_char(tz, 'DD-MON-YYYY HH:MI:SS')) virtual
)
partition by RANGE (ts)
(
PARTITION P2011010108 VALUES LESS THAN (TIMESTAMP' 2011-01-01 11:00:00'),
PARTITION P2011010109 VALUES LESS THAN (TIMESTAMP' 2011-01-01 11:00:00'),
PARTITION P2011010110 VALUES LESS THAN (TIMESTAMP' 2011-01-01 11:00:00'),
PARTITION P2011010111 VALUES LESS THAN (TIMESTAMP' 2011-01-01 11:00:00'),
PARTITION "PM_MAXVALUE" VALUES LESS THAN (MAXVALUE)
);
In our DW application we need to have partition & display the data info with TZ
This sql is hitting right partition :
SELECT COUNTRY,TZ
FROM OMC.TESTTZ F
WHERE
(F.TS >= TIMESTAMP' 2011-01-01 09:00:00'
AND F.TS < TIMESTAMP' 2011-01-01 10:00:00')
Our application is meta model design in such a that we can have only one TIME dimension.
Sql is generated based on this meta model.
So in a sql I can have TZ column alone. ( i.e. TZ is needed to show time zone info & TS to hit the partition )
Is there is any trick I can use to hide TS column completely in my application sql still hit right partition.
Thanks
February 15, 2011 - 8:30 am UTC
think about what happens with daylight savings - when there is an hour repeated.
What will happen to your data in those cases.
Will the data be right for you? Is daylight savings a concern to you?
Partition on TIMESTAMP WITH TIME ZONE
A reader, February 15, 2011 - 5:46 am UTC
hi Tom,
some typo in my post.
my requirement is :
can I hide 'TS' column completely in my application, but still hit correct partition.
Partition on TIMESTAMP WITH TIME ZONE
A reader, February 15, 2011 - 9:45 am UTC
hi Tom,
daylight savings is not a concern.
Data is more for analysis & reporting purpose there is no transaction.
February 15, 2011 - 12:18 pm UTC
well, then think about what will happen if you put in data with different time zones.
I put in these two TZ times, the corresponding TS times are:
ops$tkyte%ORA11GR2> select tz, ts from testtz;
TZ
---------------------------------------------------------------------------
TS
---------------------------------------------------------------------------
01-JAN-09 11.00.00.000000 AM -08:00
01-JAN-09 11.00.00.000000 AM
01-JAN-09 11.00.00.000000 AM -05:00
01-JAN-09 11.00.00.000000 AM
so, is that correct for you
I mean - you are the only one situated to tell us if you are going to get the correct data in response to your query. You'd have to fully define "correct" first.
Time Zone lost when TRUNC( )
A reader, February 18, 2011 - 6:52 am UTC
Hi Tom,
I am not sure I am missing some basic here. its correct for our application.
In our DW application we are collecting telecom network traffic.
We do summery report on traffic for every hour /day /week.. i.e. group by TRUNC( time,'HH24')
We want to see 10:00 traffic of East & 10:00 traffic Pacific as independently.
there is no relation between East & Pacific time.
As of now we are using "TIMESTAMP" column, to store time by removing time zone info from the data.
we can't use "TIMESTAMP WITH local TIME ZONE" where time is convert to server time.
i.e. Time is a dimension, if its changed then it will change the meaning.
Report will show as traffic is happening on server time that’s not correct.
** Problem we are facing is, user want to see real time traffic, he sits in Central TZ.
User SQL has where clause as his time (Central Time -1min ) , for this SQL we are getting older data of East & no data from West :-)).
Say user fires as query at 10 AM of central time, query gets older data of east, its already 11 AM in east & no data from west its only 8:00 AM in west.
I don't know how to solve this problem.
Ideally this will get solved if store the time in "TIMESTAMP WITH local TIME ZONE" column but this has problem of changing dimension value I said above.
Thanks
February 18, 2011 - 9:11 am UTC
trunc works on dates - not timestamps.
when you call trunc, we implicitly convert the timestamp into a date, thus losing the timezone.
see the comment above
... Time Zone lost when TRUNC( ) February 4, 2011 - 4am Central time zone ...
this was just covered. It shows one possible method of truncating while preserving the time zone.
Partition on TIMESTAMP WITH TIME ZONE
A reader, February 18, 2011 - 9:31 am UTC
Hi Tom,
>> trunc works on dates - not timestamps
-this is very clear to me from your reply.
Can you check & tell me other problem i desc in above thread.
>> February 18, 2011 - 6am Central time zone
Regards
February 18, 2011 - 9:42 am UTC
I'm getting confused here.
What is the data type of the column you have in the database right now. The exact datatype.
And I'm assuming you just want to pull records between two timestamps that include timezones. correct?
Partition on TIMESTAMP WITH TIME ZONE
A reader, February 18, 2011 - 10:15 am UTC
Hi Tom,
i am having TIMESTAMP as of now.
we want to have 'TIMESTAMP with Time zone' -- but partition is not supported for this.
we want to pull newly arrived data from different TZ.
regards
February 18, 2011 - 11:53 am UTC
then store everything in UTC and partition by that. If all times are stored in a known (single) timezone - we can convert them to any other timezone.
Oleksandr Alesinskyy, February 18, 2011 - 12:44 pm UTC
Partitioning is as well supported for TIMESTAMP WITH LOCAL TIMEZONE.
That said I hardly can understand why Oracle badly mixes 2 unrelated concepts - timestamp storage and timestamp presentation.
Oleksandr Alesinskyy, February 24, 2011 - 2:54 pm UTC
I have one remark (or question?) regarding timestamp-based range partitioning and optimizer.
Today I observed following behavior (11.1.0.7):
1. A table partitioned on TIMESTAMP WITH LOCAL TIMEZONE column (1 partition per day).
2. A query employs BETWEEN on this column with upper and lower border expressed as timestamp literals.
3. If these literals do not include timezone, partition elimination successfully occurs (and it does not matter if the session timezone matches timezone of partition borders).
4. If these literals do include timezone partition elimination does not occurs (and it does not matter if the session timezone matches timezone of partition borders).
Any idea why?
Here is the sample code
CREATE TABLE LOG_ENTRY
(ENTRY_TIMESTAMP TIMESTAMP(3) WITH LOCAL TIME ZONE NOT NULL)
PARTITION BY RANGE (ENTRY_TIMESTAMP)
( PARTITION P_20110215000000 VALUES LESS THAN (TIMESTAMP' 2011-02-16 00:00:00,000000000Europe/Berlin CET'),
PARTITION P_20110216000000 VALUES LESS THAN (TIMESTAMP' 2011-02-17 00:00:00,000000000Europe/Berlin CET'),
PARTITION P_20110217000000 VALUES LESS THAN (TIMESTAMP' 2011-02-18 00:00:00,000000000Europe/Berlin CET'),
PARTITION PMAX VALUES LESS THAN (MAXVALUE)
);
alter session set time_zone='GMT';
select * from log_entry where entry_timestamp between timestamp'2011-02-17 00:00:00' and timestamp'2011-02-17 23:59:59.999';
select * from table(dbms_xplan.display_cursor);
alter session set time_zone='CET';
select * from log_entry where entry_timestamp between timestamp'2011-02-17 00:00:00' and timestamp'2011-02-17 23:59:59.999';
select * from table(dbms_xplan.display_cursor);
alter session set time_zone='GMT';
select * from log_entry where entry_timestamp between timestamp'2011-02-17 00:00:00 GMT' and timestamp'2011-02-17 23:59:59.999 GMT';
select * from table(dbms_xplan.display_cursor);
alter session set time_zone='CET';
select * from log_entry where entry_timestamp between timestamp'2011-02-17 00:00:00 GMT' and timestamp'2011-02-17 23:59:59.999 GMT';
select * from table(dbms_xplan.display_cursor);
alter session set time_zone='GMT';
select * from log_entry where entry_timestamp between timestamp'2011-02-17 00:00:00 Europe/Berlin CET' and timestamp'2011-02-17 23:59:59.999 Europe/Berlin CET';
select * from table(dbms_xplan.display_cursor);
alter session set time_zone='CET';
select * from log_entry where entry_timestamp between timestamp'2011-02-17 00:00:00 Europe/Berlin CET' and timestamp'2011-02-17 23:59:59.999 Europe/Berlin CET';
select * from table(dbms_xplan.display_cursor);
February 24, 2011 - 7:02 pm UTC
if you look at the filter when you don't use a timestamp, you see:
1 - filter(TIMESTAMP' 2011-02-17 00:00:00.000000000'<=TIMESTAMP' 2011-02-17
23:59:59.999000000')
3 - filter(("ENTRY_TIMESTAMP">=TIMESTAMP' 2011-02-17 00:00:00.000000000' AND
"ENTRY_TIMESTAMP"<=TIMESTAMP' 2011-02-17 23:59:59.999000000'))
when you use a timestamp, the filter is:
2 - filter((SYS_EXTRACT_UTC(INTERNAL_FUNCTION("ENTRY_TIMESTAMP"))>=TIMESTAMP'
2011-02-17 00:00:00.000000000' AND SYS_EXTRACT_UTC(INTERNAL_FUNCTION("ENTRY_TIMESTAMP"))<
=TIMESTAMP' 2011-02-17 23:59:59.999000000'))
you can see the functions being applied to the timestamp column - which obviates partition elimination.
a timestamp with local timestamp is really just a timestamp without a timezone. From Expert Oracle Database Architecture:
<quote>
TIMESTAMP WITH LOCAL TIME ZONE: This is a fixed-width 7- or 11-byte date/time datatype just as the TIMESTAMP is; however, it is time zone sensitive. Upon modification in the database, the TIME ZONE supplied with the data is consulted, and the date/time component is normalized to the database time zone. So, if you were to insert a date/time using the time zone U.S./Pacific and the database time zone was U.S./Eastern, the final date/time information would be converted to the Eastern time zone and stored as a TIMESTAMP would be. Upon retrieval, the TIMESTAMP stored in the database would be converted to the time in the session’s time zone.
</quote>
so, the timestamp with local time zone is stored with 7 bytes (or 11 if you have microseconds) and is in effect just a timestamp. When you compare it to a true timestamp with timezone - you need to "promote it" to the timestamp with timezone type - it needs to be promoted (and changed) to the 13 byte representation.
Beware implicit conversions!!! They are evil.
Oleksandr Alesinskyy, February 25, 2011 - 6:47 am UTC
Firstly, I assume that writing if you look at the filter when you don't use a timestamp you meant if you look at the filter when you don't use a timezone
Secondly, I am wondering why Oracle applies functions to the column value instead of converting a specified time-zoned literals to the session timezone (do I understand correctly that timestamp literals specified without timezone interpreted in session timezone)?
February 25, 2011 - 8:53 am UTC
yes, that is what I meant - timezone, not timestamp in that context, sorry about that.
Secondly, I am wondering why Oracle applies functions to the column value instead of converting a specified time-zoned literals to the session timezone (do I understand correctly that timestamp literals specified without timezone interpreted in session timezone)?
because implicit conversions tend to go "up"
when we compare a string to a number, the string is converted up - into a more specific type - that of number.
for example. When you compare a timestamp to a timestamp with timezone - the rule goes from the less specific to the more specific type.
As is the case in most implicit conversions I've seen in languages.
Oleksandr Alesinskyy, February 28, 2011 - 9:47 am UTC
From my point of view TIMESTAMP WITH TIMEZONE is neither less nor more specific than TIMESTAMP WITH LOCAL TIMEZONE
February 28, 2011 - 11:27 am UTC
You might need to change your perspective.
Timestamps with local timezones are not stored with a timezone at all. They are normalized upon insert - converted into a timestamp with the appropriate time.
but what should be clear is that....
timestamp with timezone is not the same type as timestamp with local timezone.
And the hierarchy is such that timestamp with timezone is a more "precise" one - it represents a date, a time and a timezone - whereas the other represents a date and a time - and gets the timezone base from the database itself. Therefore, when comparing these two different datatypes (they are two different datatypes), one must be converted into the other - and the rule is .... stated above.
Whenever you compare two attributes/expressions that have different datatypes - you MUST beware of the implicit conversions that have to take place.
Which means (to me anyway) that you should code all such comparisons using explicit conversions...
Oleksandr Alesinskyy, February 28, 2011 - 3:31 pm UTC
From my point of view a conversion of TIMESTAMP WITH LOCAL TIME ZONE should be done differently - for the matter of (such)comparison it simply should be treated as a timestamp with the session timezone attached.
March 01, 2011 - 8:01 am UTC
But - and this is important:
things work the way they work, not the way "we" would have done it. This is a truism.
They are two different datatypes - completely - different sizes, different meanings. You have to convert one into the other - it is that simple. It is an IMPLICIT conversion. If you don't like the way that particular IMPLICIT conversion is done - then by all means - be explicit about it.
Anytime you compare datatype X to datatype Y - this sort of stuff happens.
TIMESTAMP WITH LOCAL TIMEZONE is one datatype
TIMESTAMP WITH TIMEZONE is (by its very definition) a different one
Otherwise, there would just be a single datatype
timestamp with some timezone
Hide day in date column
banyan384, May 24, 2011 - 4:49 pm UTC
For security purpose, I want to store just month and year instead of the day, month, year and time of date data type column.
I want to use date data type, but want to store month and year only.
I don't want to store 1st of each month also.Can we do any magic hear instead of converting date column to varchar2/number.?
May 25, 2011 - 11:21 am UTC
If you do not want to store the 1st of the month (or some other day), then you cannot use a date type.
However, I would encourage you to use the 1st if possible - it'll avoid lots of conversions in the future.
help this
venkata, May 30, 2011 - 10:47 am UTC
Hi Tom,
PS: don't say my code is....
i am trying to get date and time in zurich time format how can i do that. my current time is UK time.
May 31, 2011 - 10:55 am UTC
Venkata -
cool it with the "PS" stuff already. If you don't post something so horribly wrong, I won't say "I hate your code". That is all.
ops$tkyte%ORA11GR2> select sysdate,
2 from_tz( cast(sysdate as timestamp), 'US/Eastern' ),
3 from_tz( cast(sysdate as timestamp), 'US/Eastern' ) at time zone 'Europe/Zurich'
4 from dual;
SYSDATE
---------
FROM_TZ(CAST(SYSDATEASTIMESTAMP),'US/EASTERN')
---------------------------------------------------------------------------
FROM_TZ(CAST(SYSDATEASTIMESTAMP),'US/EASTERN')ATTIMEZONE'EUROPE/ZURICH'
---------------------------------------------------------------------------
31-MAY-11
31-MAY-11 11.53.51.000000 AM US/EASTERN
31-MAY-11 05.53.51.000000 PM EUROPE/ZURICH
If you have a date datatype - cast as timestamp and tell us what timezone it is in and use "at time zone".
If you have a timestamp with timezone - just use "at time zone"
Some basic solutions
sftranna, February 29, 2012 - 12:22 am UTC
Reader, March 19, 2012 - 10:41 am UTC
Tom,
I store time in EST in my table. GMT+10 is standard time and does not count for DST. Please correct me if I am wrong.
I used new_time to convert EST to GMT. Can you please tell me how to convert EST to GMT+10?
select sysdate,
new_time(sysdate,'est','gmt') est_gmt,
dbtimezone,
sessiontimezone
from dual
SYSDATE EST_GMT DBTIME SESSIONTIMEZONE
------------------------ ------------------------ ------ ----------------
19-MAR-12 11:39:42 19-MAR-12 16:39:42 -04:00 -04:00
March 19, 2012 - 8:45 pm UTC
I store time in EST in my table. GMT+10 is standard time and does not count for
DST. Please correct me if I am wrong.
I don't know what to say to that?
do you really store EST in your table - or - do you store whatever the OS returns at the current date/time which is subject to the plus or minus one hour shift? GMT is 'fixed', GMT+10 is 'fixed', EST is 'fixed' but most OS's are configured to return the time they see which can be EST or EDT depending on the time of the year.
that'll convert from est to gmt - but I'm not sure if that is what you really want or not.
using SYSDATE , but can't change timezone *help~
A reader, April 27, 2012 - 4:17 am UTC
hi tom,
i am hitting a serious problem.
throughout my applications in the db, i am using sysdate all over.
recently, we migrated to amazon cloud and their machines are running at UMT 0000 wheares we need it to be at +0800hr
and they are unable to change the machine date for us.
Understand that SYSDATE is getting its value from the OS time/date, thus there is no way i can amend the OS date/time.
--------------------------
Q1) should i change all my codes to SYSDATE+8/24 ?
or
Q2) is there anyway i can "hijack" any external or internal application that call SYSDATE in database to add +8 into the result ?
q3) what would you suggest ? do set session timezone to 0800 and use localtimestamp in replace of sysdate ?
but how do i make sure other web components wouldn't call SYSDATE, there are too much codes to change.
Regards,
Noob
Timezone issue
Deepak, December 10, 2012 - 5:49 am UTC
Hello Tom,
We also have similar issue related to time zone. Our client works in Indian time zone(IST) and Singapore time zone (SGT) currently as separate databases. And we want to migrate data from SGT time zone database to IST time zone database.
We exported data from SGT time zone database and imported the same in IST time zone database. We thought the data with SGT dates will be shown as IST dates but the database is imported as is.
Now our thinking is that
a. Treat IST data and SGT data separately. So this will be a problem if we change that for each connection in SGT and IST, for this we want to have 2 separate connection pooling for SGT and IST. For IST connection pooling no changes are required but for SGT connection pooling the OS time zone and database client time zone should be changed to SGT.
Please let us know whether or not our thinking is correct.
Thanks & Regards,
Deepak.
December 14, 2012 - 2:33 pm UTC
you don't even tell me what the datatype of the column was or anything :(
oracle data format
cynthia, March 22, 2013 - 11:26 am UTC
please i wish to know if my site could be connected with relevant answer on the topic stated above...www.unn.edu.ng.thanks
March 25, 2013 - 2:12 pm UTC
you can link to anything you want, it is the internet after all.
implicit conversion
Lal, August 30, 2013 - 1:10 pm UTC
Tom,
I have the following query which is executed from a java application using bind variables.
select col1,col2 from tab1 where dcol>?
The datatype of column dcol is date, also an index exists on tab1 table with dcol column.
The query does not use index, since the java team is setting a timestamp value for the bind variable, which causes an implicit datatype conversion.
The java team says, they can only set the value as a timestamp, if they need to pass a date and time value for the above query.
To resolve this one option is to convert the date column to a timestamp datatype.
My question is will there be any performance overhead of using a timestamp column over a date column.?
If not can i convert all date columns which stores time to a timestamp datatype rather than a date datatype?
Do you foresee any issues in this?
As always waiting for your expert opinion.
September 04, 2013 - 6:27 pm UTC
The java team says, they can only set the value as a timestamp, if they need to
pass a date and time value for the above query.
the java team is either lying or ignorant of how to do it.
of course they can bind a date - OF COURSE they can bind a date. OF COURSE they can. - geez - of course they can. ask them what happened in the 1990's when timestamp as a type didnt' even exist?????????? *of course they can* bind a date.
and binding a date is what they should do. please do not change your schema - teach them that 'binding a date is natural, normal, and correct'. do not accommodate them with a schema change - that would be a slippery slope.
at the very least, if they say "we only want to bind a timestamp" then have them:
where dcol > ( cast ? as date );
GMT Timezone
A Reader, January 30, 2014 - 11:46 am UTC
Hi Tom,
If the database timezone is set to GMT and if the below query is executed in SQL on the 1st of August 2014 (just an example date), what will be displayed?
01-AUG-2014 15:00:00 or 01-AUG-2014 14:00:00
Best Regards
GMT Timezone
A Reader, January 30, 2014 - 2:00 pm UTC
Apologies, please ignore my previous question as I had missed the important bits.
If the database timezone is set to GMT and if the below query is executed in SQL on the 1st of
August 2014 at 3PM (UK will be in BST time)
SQL> celect sysdate from dual;
what will be displayed?
01-AUG-2014 15:00:00 or 01-AUG-2014 14:00:00
Best Regards
new link for timedate.zip
Praveen Ray, July 10, 2015 - 11:21 am UTC
Imam, January 29, 2021 - 9:58 am UTC
Extract time zone from TIMESTAMP column
A reader, March 12, 2024 - 12:16 am UTC
I have inserted 'systimestamp' in TIMESTAMP(6) datatype column.
When i try to query the TIMESTAMP column as is and with 'at time zone 'America/New_York' , I am getting +1 hr for timezone.
Just wondering why this difference is.
Note: Database default uses US EDT time as the server is set to US/Eastern.
SQL> select
RECORDED_DATE_START,
RECORDED_DATE_START at time zone 'America/New_York' RECORDED_DATE_START_TZ
from t1_tab
/ 2 3 4 5
RECORDED_DATE_START RECORDED_DATE_START_TZ
--------------------------------------------------------------------------- ---------------------------------------------------------------------------
11-MAR-24 12.12.22.607188 PM 11-MAR-24 01.12.22.607188 PM AMERICA/NEW_YORK
SQL>
<code>
1* create table t1_tab(recorded_date_start TIMESTAMP(6))
SQL> /
Table created.
SQL> insert into t1_tab values(systimestamp) ;
1 row created.
SQL> commit ;
Commit complete.
SQL> select * from t1_tab ;
RECORDED_DATE_START
---------------------------------------------------------------------------
11-MAR-24 12.12.22.607188 PM
March 14, 2024 - 2:20 am UTC
A "timestamp" is just that and nothing else. It does not have any time zone associated with it.
When you inserted systimestamp into a "timestamp" column the time zone information was dropped/lost, so it will always come back as per the timezone that systimestamp had at insert time.
Only "timestamp with time zone" will capture the time zone at the time of data creation.
To be useful, a "timestamp" column needs a (single) implicit time zone maintained by your application. The recommended one is UTC, so '+00:00'. Systimestamp returns values in the system time zone, which may be sensitive to daylight-saving time changes. In autumn, you may see one hour repeated when you switch from summer to winter time. Such a repeated hour cannot be interpreted properly. Also, when you change the OS time zone, the systimestamp's time zone changes as well. This is not good for the already stored values. Hence the recommendation is to "assign" UTC as the implicit time zone.
If you decide to normalize "timestamp" column values to UTC, then you may populate the column using the expression SYSTIMESTAMP AT TIME ZONE '+00:00' and query it using the expression FROM_TZ(<column>, '+00:00') AT TIME ZONE <of your choice>.