SQL & PL/SQL :: Date Math And Daylight Savings Time
May 25, 2010
I am trying to get the number of seconds between March 3 2010 and march 31 2010 in Oracle. I am in Eastern time in the US. Everything I try just keeps coming up with 30 days * 86400 seconds per day = 2592000. 1 hour was lost when we switched to daylight savings time so the correct answer is 2588400.
How do a create a function in Oracle that will give me the number of seconds between 2 dates or timestamps that is aware of the loss of an hour in March and the gain of an hour in November?
How can I convert a DATE to EPOCH time taking care of daylight savings?
I tried with this code but there is a difference of 36000 seconds. eg:Sysdate_To_Epoch('04-Sep-2012') gives 1346716800 whereas it should give 1346680800.
CREATE OR REPLACE FUNCTION Sysdate_To_Epoch(v_date IN DATE) RETURN NUMBER IS
During the daylight savings time our scheduler jobs are running either an hour before/after depending upon the time switch.
I went through the Oracle documentation and found below suggestions which I have already tried in vain.
Document says scheduler first picks the timezone from the start date of the job if provided so i tried setting the start date using the TO_TIMESTAMP_TZ('2012/01/22 18:50:00 US/Eastern','yyyy/mm/dd hh24:mi:ss tzr') which did not fix the problem. I have noticed that oracle automatically converted into tzh:tzm format.
second solution: setting default timezone of scheduler to the TZR i.e (US/Eastern) instead of the TZh:TZM value. I did that using below script
BEGIN DBMS_SCHEDULER.set_scheduler_attribute ( attribute => 'default_timezone', value => 'US/Eastern'); END;
above 2 solutions did not work for me. I have read on internet from some article that below query should return something like
"4/23/2012 11:02:13 US/Eastern" after setting the default timezone of scheduler to TZR but I am still getting "4/23/2012 11:02:13.715816000 AM -04:00". select dbms_scheduler.stime from dual;
When I try to extract the date tag value from XML data, the time stored in 20120602153021 format i.e., YYYYMMDD24HHMISS format. The following statement extracts only date as 02-JUN-12 however do not extract the time part.
If I try the same in SQLplus with to_date it works however fails in PL/SQL.
XML data: <?xml version="1.0"?> <RECORD> <REGTIMESTAMP>20120601130010</REGTIMESTAMP> </RECORD>
PL/SQL Extract:
CURSOR c_xml_record IS SELECT extract(value(d), '//ACTIVATIONTS/text()').getStringVal() AS REGTIMESTAMP, FROM t_xml_data x, [code].......
I want to reset my date to this format: 12/31/2012 11:59:59 PM - see code below:
DECLARE v_latest_close DATE; BEGIN v_latest_close := TO_DATE ('12/31/2012 23:59:59 ','MM/DD/YYYY HH24:MI:SS'); DBMS_OUTPUT.PUT_LINE('The new date format is : '|| v_latest_close); END;
the code above displays only : 12/31/2012 instead of 12/31/2012 11:59:59 PM
I am having problems with the XMLTable function. I cant get it to see the entire date/time value in a date field. This wont work
select x1.* from XMLTABLE('/DOCUMENT' passing xmltype('<DOCUMENT><STR>abc def ghi</STR><NUM>1234</NUM><DT>2013-02-17T04:24:02</DT></DOCUMENT>') columns STR varchar2(25), NUM number, DT date) x1;
However if I change the DT tag to just the date only "2013-02-17" it works. Why wont Oracle see the entire date/time format even if its ISO 8601 compliant?
I have a user interface where the user can enter a formula using a set of parameters and operators. For eg , the user is given a list of parameters, say : Width, Height , Gauge. The list of operators include standard math functions : + - / * ^
He can select and create a formula like gauge * width. This is stored in a table. At a later time a job is scheduled to evaluate the formula based on parameters for width, gauge and height.
I can evaluate the expression by substituting each parameter.
I can do this using dynamic sql. However I'm not sure how to evaluate the ^ symbol. The equivalent of this is power function.
So if a user enters (height * 2) + (width^2) . How do i evaluate this. I am doing this in pl/sql.
is there possible to retrieve date with its time in oracle ,if we didnot provide time while insertion? like hiredate column of emp table can we get hiredate of each emp with its time of joining. i am using "11g Enterprise Edition Release 11.2.0.1.0"
I have a question with Oracle sql developer, i've installed oracle 11g express edition, and i want to insert values 'date' and 'time', but i only got the resultat like this:
Who can tell me how can i do to show just date info in 'date' and same for the 'time'?
I am converting data from an old paradox table to a new oracle table, one of the problems im having is incompatibility with date and time formats:
some columns contain times in the format : "00:00:00" eg..... "15:00:00" some columns have date in the format: "dd/mm/yyyy" eg....... "21/08/2000" some columns have time and date eg.. "05/09/2000 15:49:39"
Currently I have the data held in tables within an access database, and in CSV format.
eg, I have dates like 03/04/2010 which i need to be 03-APR-10....
how I can get the following into Oracle date formats? there is over 1000 records so manual conversion is out of the question
SQL> desc guide; Name Null? Type ----------------------------------------- -------- ---------- GUIDE_ID NOT NULL NUMBER(20) CHANNEL_ID NOT NULL NUMBER(5) SHOW_DATETIME DATE [code]....
when I run this query it only displays the date but no time.how to display it with the date and time?
I have a sql id,I want to figure out how many number of times that sql was executed since last 30 days.I can get some details from dba_hist_active_sess_history.
But how to get count(total executions in one day )like wise for 30 days or so.and How can i map SNAP_ID to date&time .
I have a table test and it contains date datatype column JDATE;
SQL> desc test Name Null? Type ----------------------------------------- -------- -------------------- EMPNO NUMBER EMPTYPE VARCHAR2(20) SALARY NOT NULL NUMBER JDATE DATE DEPTNO NOT NULL NUMBERSELECT TO_CHAR(JDATE,'DD/MM/YYYY HH24:MI:SS') JDATE FROM DUAL; JDATE 1/11/2010 4:17:29 PM 1/11/2010 4:15:47 PM 1/5/2010 3:50:44 PMIn the above case i want to update test table and increase the minut of each row by 1 minut.
like for 1/11/2010 4:17:29 PM It would be like 1/11/2010 4:18:29 PM. for 1/11/2010 4:15:47 PM it would be like 1/11/2010 4:16:47 PM.Can we do this...
select TO_DATE( TO_CHAR(CAST((B.EOAWDTTM_MODIFIED) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),'YYYY-MM-DD') from PS_SR_EX_ER_AW_VW B where rownum < 5
and It gives me the error
ORA-01830: date format picture ends before converting entire input string 01830. 00000 - "date format picture ends before converting entire input string"
When I select sysdate from dual it shows me the time in EST. But when I try to fetch the time from a column which is being updated almost every minute with sysdate of the time it is being updated, it shows me the time in UTC that is 4 hours added to the time when it was logged.
SQL> select * from nls_database_parameters;
PARAMETER VALUE ------------------------------ ---------------------------------------- NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS ., NLS_CHARACTERSET WE8ISO8859P1 NLS_CALENDAR GREGORIAN NLS_DATE_FORMAT DD-MON-RR NLS_DATE_LANGUAGE AMERICAN NLS_SORT BINARY NLS_TIME_FORMAT HH.MI.SSXFF AM [code]..
I am using the same query on a different table and there's no problem on it:
select to_char(ENTRYDATE,'MM-DD-YYYY HH24:MI:SS') from TBL_XX_PROGRAM where ENTRYDATE > sysdate -10 and rownum <10 03-03-2011 17:06:48 03-03-2011 17:06:48 03-03-2011 16:59:08 03-03-2011 16:59:08 03-03-2011 12:04:21
I checked the DDL of both tables and the only difference is that the TBL_XX_PROGRAM have the ENTRYDATE field defined as not null and the ENTRYDATE on the TBL_BANDWIDTH_READS does not have the same constraint, could it affect?