SQL & PL/SQL :: Timestamp Column Whose Values Are In CET Timezone Needs To Be Converted To EST
Mar 6, 2012
I have a requirement in which a particular a timestamp column (date1) whose values are in CET timezone needs to be converted to EST and day light savings should be taken care of in the conversion logic. I should not use any ddl statements for altering the timezone and all.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
how to subract the timestamp values.
SELECT TO_TIMESTAMP ('10-Sep-02 14:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF') FROM DUAL;
resolve issue while modified the user function code for returns the values as timestamps
---function code
create or replace function fun_test_timestamp(P_HOUR varchar2) return varchar2 is sql_stmt varchar2(1000); begin
[Code].....
Input:- select fun_test_timestamp('5') from dual;
Output:-
SELECT CURRENT_TIMESTAMP - INTERVAL '5' HOUR FROM DUAL;
Modified Fun Code:
create or replace function fun_test_timestamp(P_HOUR varchar2) return timestamp is sql_stmt varchar2(1000);
[Code]...
Input:- select fun_test_timestamp('5') from dual;
Output:-
ORA-00911: invalid character ORA-06512: at "NETVERTEXTRUNK.FUN_TEST_TIMESTAMP", line 8 00911. 00000 - "invalid character"
*Cause: identifiers may not start with any ASCII character other than letters and numbers. $#_ are also allowed after the first character. Identifiers enclosed by doublequotes may contain any character other than a doublequote. Alternative quotes (q'#...#') cannot use spaces, tabs, or carriage returns as delimiters. For all other contexts, consult the SQL Language Reference Manual.
I have one table with a columntype "timestamp" say xyz is the name of the column.Suppose today is 15th Sept So I want to fetch all those records whose value is between 14th Sept 00:00:00 and 14th Sept 23:59:59.
I want to write a query to get the time stamp from only one date column,
I tried using a group by clause but getting error "not a group by exp."
Below is the query
SELECT ProdID,ProdRequestID, SUBSTR((max(EVENTTIMESTAMP) - min(EVENTTIMESTAMP)), 18,2)Execution_Time FROM LOG_TIMESTAMPS where ProdID = 1680988889 group by ProdRequestID ProdID||ProdRequestID ||EVENTTIMESTAMP
[code]....
In the above i am looking for a diference on ProdRequestId,
INSERT INTO shiftsample (Empid, PPDate, Inpunch, outpunch)( SELECT Emp_ID, PDate, In_Punch, Out_Punch FROM ProcessDailyData WHERE PDate = to_char(2012-10-15,'yyyy-MM-dd') AND Emp_ID = '00000001' );
in this query pdate has timestamp datatype and in shift sample ppdate column has date type. so i am not able to insert value from process daily data table.
getting this error.
SQL Error: ORA-01481: invalid number format model 01481. 00000 - "invalid number format model" *Cause: The user is attempting to either convert a number to a string via TO_CHAR or a string to a number via TO_NUMBER and has supplied an invalid number format model parameter.
I have a table A with a column B timestamp(6). The tables contains around 300000 rows..I have created index 'idx' on the column B.When i compare column 'B' with systimestamp, it does not use the index, whereas if i compare 'B' with sysdate it uses the index.
Eg : select count(*) from a where b<=sysdate;
The above used the index 'idx' and executed in 1 second
select count(*) from a where b<=systimestamp;
The above does not use the index and executed in 19 seconds.
Due to some business requirements a table field needs to change from date to timestamp in order to handle the millisecs.
1>When i alter the row , for a table with 150 million recs will there be a conversion. Is there a recommended way to convert the field. Mind you this field is used as a part of composite PK.
2> There is a interfacing application which connects and copies the data to its system and is using the date type, will that application be able to continue to work without any changes, if it does not care about the millisecs.
3> Will there be performance impact on an existing application that uses the date field to sort
My scenario is to insert values into 'out' column by comparing 's' and 'IP' columns of temp table.The exact situation is at first need to go to ip column,take a value and then go to source column and check for the same value of ip which is taken previously.Then after corresponding ip of that source column should be inserted back in previous source column.
The situation is marked clearly in file which i am attaching with '--' comments at respective places.I am also pasting the code which i tried out,unfortunately it is giving error as exact fetch returns more than requested number of rows since there are duplicates in the table.I tried it using nested for loops.Also implemented using rowid,but it didnt work.
fixing the errors or if there is any new logic that can be implemented.
DECLARE i_e NUMBER(10); BEGIN FOR cur_1 IN(SELECT IP from temp where IP IS NOT NULL) LOOP FOR cur_2 IN(SELECT IP from temp where s=cur_1.IP)
I'm trying to import a xls file that I converted into a csv. I have a notes column that has carriage returns in it. This is causing SQL LDR to error out with the following error:
second enclosure string not present
It is only grabbing up until the first carriage return.how I can get it to load?
From two given tables, how do you fetch the values from two columns using values from one column(get values from col.A if col.A is not null and get values from col.B if col.A is null)?
I am new to oracle and sql in general, I received an oracle create schema that needs to be converted using non-oracle syntax. I have never seen this syntax before.
What does the following syntax mean? CODE,line_status(1:20) char(2) null
On my APEX page i have region which has sql query as source and it displays as HTML table the query result to the user.
I want to display addinonal column with a hyperlink inside, and that hyperlink would have CGI/URL-parameters which contains the other values of the HTML row.
So, let's say my APEX region queryes columns as "select c1, c2, c3, c4 ..." and displays out values "V1, V2, V3, V4" then i want to have addional output column with such hyperlink:
a href="f?p=100:7:13467554876288::NO::c1,c2,c3,c4:v1,v2,v3,v4">My link column with CGI-parameters</aHow can i create such hyperlink?
The overall idea is that the link would forward to a page which loads those values "v1,v2,v3,v4" into form fields and user can proceed from there.
I want to pass multiple column values of a row in an interactive report page to hidden items in another page through column link. And I did it successfully. However, I found I need to pass more than 3 columns of a row in this report, while a column link only permits me to pass 3 column value at most. Is there anyway that I can pass more column values to hidden items in another page?
How do i check in Table B whether it is converted correctly into words taking input or reference from table A
Consider below example:-
Table A Table B $125 Dollar One Hundred twenty Five only $45,542 Dollar Forty Five Thousand Five Forty Two Only $145.56 Dollar One Forty Five and fifty six cents Only $145,253 $35,256.65 $560,250.67
I am facing issue related to Number data while it is being converted to Varchar2, it is automatically getting rounded off after 32 decimal place.My database version is "Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production".
"First" string got rounded off to 97 (last 2 digits) instead of 9679 but for "Second" record it holds the actual value.Only thing which I could figure out while the number is getting type casted to String, it is getting rounded off to 32 decimal place.throw off some light on it and provide the solution how the original record can be kept intact without rounding off.
As per Article mentioned in Oracle Base,I have converted non-partitioned table (1 million data) into range-partition table,but,I don't see performance improvement in explain .
I need the timezone of the server that the DB sits on. I don't want the offset. I need the region name. This is because some regions use daylight savings time and some do not. Plus an SA can change the time of the OS. Another company manages our servers and DBs in production and we don't have contact with them. We could use a lookup talbe and just populate it, then update it when we find out what it is in production. The problem is that I have seen cases where the timezone on servers change. Considering the lack of contact between the teams, we really need a reliable way to get the timezone out of the DB.
We tried several ways. My list is below and I explain why this is not working.
examples:
sessiontimezone: this is the timezone of my server. In theory it should be the same as the DB. We cannot take the risk that this will be out of sync. dbtimezone: This gives the offset. Such as -5:00 for US EST. There are multiple regions that have this. Some do not use daylight savings time and some do. We would need America/New York instead.
sessiontimezone gives the timezone setting for the client. This can be altered. dbtimezone just gives the offset such as -5:00
We get data feeds from different parts of the world. We get some data based data that is local to that regions timezone. We need to partition on this field. So we need to add a field to the DB and normalize it to the time local to our DB Server. So if we get a record from california and the DB is on a server in US EAST, we add 3 hours. The offset won't work...
1. a timezone that we are getting from may not be in daylight savings time. We are partitioning by hour.
2. We would hit daylight savings time in New York before we hit it in California, so we would need to account for that in the math.
This hourly partition is a fixed and hard requirement. We need this to be absolutely accurate.
Here is what we tried:
What I want (pseudo-code): “Select XXX as timezone_region_name” to return “America/New_York” or “UTC”. It may be that the timezone was not set for the database at install time, and if it were, these queuries would work.
-- FAILED SELECT DBTIMEZONE FROM dual;
--FAILED select systimestamp, to_char(systimestamp, 'TZR'), extract (timezone_region from systimestamp) from dual;
--FAILED SELECT systimestamp AT TIME ZONE DBTIMEZONE "DB Time" FROM DUAL;
--FAILED select to_char(systimestamp, 'TZR') from dual;
I have an interesting problem. I have a server in the UK and I have three databases globally - UK (FINUKQ1), NY (FINNYQ1) and TK (FINTKQ1). The NY and TK databases will be migrated to the UK server as part of a consolidation project. Obviously, Time Zones and DST are the biggest concern for me.Here was my plan for the implementation (concentrating on Time Zone/DST issues).
Added to .profile: -------------------- if [[ "$ORACLE_SID" = "FINTKQ1" ]]; then export TZ="Asia/Tokyo" elif [[ "$ORACLE_SID" = "FINNYQ1" ]]; then export TZ="America/New_York" else unset TZ fi echo "TZ=$TZ"
Once environment has been set start database and listener.
Change database TZ: ----------------- alter database set time_zone='Asia/Tokyo'; alter database set time_zone='America/New_York';
Check database TZ: select dbtimezone from dual;
Add trigger: ------------ CREATE OR replace TRIGGER sys.timezone_check AFTER startup ON DATABASE DECLARE
[code]...
The trigger was there to put the database in restricted session if it is started in the wrong TZ and an error written to the alert log. The check was done on the dbtimezone and the dbname. Quite simple. Only issue is that it doesn't work as I wanted as dbtimezone and TZ are separate and disparate entities. i.e. I can start the TK database up in UK time (which will be incorrect for this project), and check the dbtimezone and it will show as 'Asia/Tokyo'.
Is there a better way of checking? Any trigger like this to compare TZs etc?
I have one issue My server is in france and it is in french timezone but when I query for sysdate it returns US time.
In '/etc/sysconfig/clock/' Zone= europe/paris UTC= true
echo $TZ variable is returning nothing.
sysdate = us time systimestamp= us time current_timestamp = french time current_date = french time dbtimezone= europe/warsove, sessiontimezone=+2.00( which is also europe timezone offset) tz_offset(dbtimezone)=+2.00, tz_offset(sessiontimezone)= +2.00 i.e europe os timezone= europe/paris.
This command "./emctl config agent getTZ" is also returning timezone as europe/paris
Also in "emd.properties" file "agentTZRegion" parameter is set to europe/paris
Oracle version= 11.2.0
Now I don't understand why this sysdate and systimestamp is returning "US time zone" while everything else is returning french time zone.
I'm in the process of testing a restore/recover from a simulated full system and media loss. A level 0 backup was taken from the server, and I'm trying to restore/recover to a point in time on a second server. I have created the database (with the same name) and have been able to successfully restore both the controlfile and spfile from the autobackup.
how does RMAN treat the "set until time"? The level 0 was taken on a database/server that is in CST while the database I'm trying to do a restore/ recover to is in EST. So when trying to do a point in time recovery, should I specify the time in EST or CST?
I'm going to migrate my current db 9.2.0.8 (Timezone version is 1!) to a new server 11.2.0.3, so I was wondering if I must upgrade timezone version of my current 9iR2 to version 4! Before upgrade it...Btw my new server has timezone v14.