Forum Stats

  • 3,728,531 Users
  • 2,245,647 Discussions
  • 7,853,577 Comments

Discussions

Reg: date with hour in parititon

chandra_1986
chandra_1986 Member Posts: 255 Blue Ribbon

HI experts,


I need to select the date with hour from 30th march 00:00 EST  to 30th March 23:59 EST

select * from at_ant_partition for(TO_DATE('30-MAR-2021','DD-MON-YYYY')) where id='LAS921'



How can i achieve it by adding in above query condition,.

Best Answer

  • James Su
    James Su Member Posts: 1,047 Silver Trophy
    Accepted Answer

    If your table is partitioned by this date column, then simply add it to the where condition.

    select * from at_ant_partition

     where id='LAS921'

        and date_column >= date '2021-3-30' 

        and date_column < date '2021-3-31'

Answers

  • chandra_1986
    chandra_1986 Member Posts: 255 Blue Ribbon

    Below is the query used.


    select * from at_ant partition for (TO_DATE('30-MAR-2021','DD-MON-YYYY')) where id='LAS921';

  • chandra_1986
    chandra_1986 Member Posts: 255 Blue Ribbon

    Hi Team,


    Any suggestions,

    I need to select the date with hour from 30th march 00:00 EST to 30th March 23:59 EST

    select * from at_ant partition for (TO_DATE('30-MAR-2021','DD-MON-YYYY')) where id='LAS921';

  • James Su
    James Su Member Posts: 1,047 Silver Trophy
    Accepted Answer

    If your table is partitioned by this date column, then simply add it to the where condition.

    select * from at_ant_partition

     where id='LAS921'

        and date_column >= date '2021-3-30' 

        and date_column < date '2021-3-31'

  • chandra_1986
    chandra_1986 Member Posts: 255 Blue Ribbon

    Thanks experts,


    I need to select hourly basis from the above:


    I need to select the date with hour from 30th march 00:00 EST to 30th March 23:59 EST

  • James Su
    James Su Member Posts: 1,047 Silver Trophy

    Please provide the CREATE TABLE and INSERT script to setup a quick example and post the expected result you want to get.

    chandra_1986
  • chandra_1986
    chandra_1986 Member Posts: 255 Blue Ribbon

    HI Experts,


    We have below sql create and insert statements for above table AT_ANT, trying to select 30th Data from table:

     from 30th march 00:00 EST to 30th March 23:59 EST.


    select * from at_ant partition for (TO_DATE('30-MAR-2021','DD-MON-YYYY')) where id='LAS921';


     CREATE TABLE "ANTREPORT"."AT_ANT" 

      ( "ID" NUMBER, 

    "ENTID" VARCHAR2(250 BYTE), 

    "EVENTNAME" VARCHAR2(250 BYTE), 

    "TIMESTAMP" TIMESTAMP (6), 

    "EVENTMETHOD" VARCHAR2(250 BYTE), 

    "RESOURCENAME" VARCHAR2(500 BYTE), 

    "TARGETTYPE" VARCHAR2(250 BYTE), 

    "OPERATIONNAME" VARCHAR2(500 BYTE), 

    "FUNCTIONSTATUS" VARCHAR2(250 BYTE), 

    "PAGEID" VARCHAR2(1000 BYTE), 

    "AN" VARCHAR2(500 BYTE), 

    "JOBID" VARCHAR2(1000 BYTE), 

    "RID" VARCHAR2(1000 BYTE), 

    "JOBTYPE" VARCHAR2(500 BYTE), 

    "UID" VARCHAR2(1000 BYTE), 

    "TECHREGION" VARCHAR2(100 BYTE), 

    "MGMTREPORTINGFUNCTION" VARCHAR2(1000 BYTE), 

    "RECORDPUBLISHINDICATOR" VARCHAR2(25 BYTE), 

    "VERSION" VARCHAR2(25 BYTE)

      ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 

     STORAGE(

     BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

     TABLESPACE "AT_ANT_REPORTING_DATA" 

     PARTITION BY RANGE ("TIMESTAMP") INTERVAL (NUMTODSINTERVAL(1,'DAY')) 

     (PARTITION "P_FIRST" VALUES LESS THAN (TIMESTAMP' 2021-01-01 00:00:00') SEGMENT CREATION IMMEDIATE 

     PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 

     NOCOMPRESS LOGGING 

     STORAGE(INITIAL 16777216 NEXT 16777216 MINEXTENTS 1 MAXEXTENTS 2147483645

     PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

     BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

     TABLESPACE "AT_ANT_REPORTING_DATA" ) ;

    REM INSERTING into ANTREPORT.AT_ANT

    SET DEFINE OFF;

    Insert into ANTREPORT.AT_ANT (ID,ENTID,EVENTNAME,TIMESTAMP,EVENTMETHOD,RESOURCENAME,TARGETTYPE,OPERATIONNAME,FUNCTIONSTATUS,PAGEID,AN,JOBID,RID,JOBTYPE,UID,TECHREGION,MGMTREPORTINGFUNCTION,RECORDPUBLISHINDICATOR,VERSION) values (1568217969,'LAS921','HistoryInformation',to_timestamp('05-FEB-21 08.30.00.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'),'getHistoryInfoResponse','getHistoryInfo','mS','getHistoryInfo','SUCCESS','JobDetailsPage','264284404','W1034020619','W1034020619','IAX','j029','W','N','N','0');

    Insert into ANTREPORT.AT_ANT (ID,EVENTID,EVENTNAME,TIMESTAMP,EVENTMETHOD,RESOURCENAME,TARGETTYPE,OPERATIONNAME,FUNCTIONSTATUS,PAGEID,AN,JOBID,WRID,JOBTYPE,UID,TECHREGION,MGMTREPORTINGFUNCTION,RECORDPUBLISHINDICATOR,VERSION) values (1568217970,'LAS1008','CloseBbnmsInitiate',to_timestamp('05-FEB-21 08.30.00.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'),'Response','CloseBbnmsInitiate','mS',null,null,'CloseDtvJob','264606507','W1036018351','W1036018351','MRJJHIHIX','s515x','W','N','N','0');

    Insert into ANTREPORT.AT_ANT (ID,EVENTID,EVENTNAME,TIMESTAMP,EVENTMETHOD,RESOURCENAME,TARGETTYPE,OPERATIONNAME,FUNCTIONSTATUS,PAGEID,AN,JOBID,WRID,JOBTYPE,UID,TECHREGION,MGMTREPORTINGFUNCTION,RECORDPUBLISHINDICATOR,VERSION) values (1568217971,'LAS1500','CHECKWIFACOUNT',to_timestamp('05-FEB-21 08.30.00.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'),'Reporting','ADDEXTCOUNT',null,null,null,'CloseJobView','105333602','W1032002968','102706295','IRJ3QXX','j91c','W','N','N','0');

    Insert into ANTREPORT.AT_ANT (ID,EVENTID,EVENTNAME,TIMESTAMP,EVENTMETHOD,RESOURCENAME,TARGETTYPE,OPERATIONNAME,FUNCTIONSTATUS,PAGEID,AN,JOBID,WRID,JOBTYPE,UID,TECHREGION,MGMTREPORTINGFUNCTION,RECORDPUBLISHINDICATOR,VERSION) values (1568217972,'LAS885','storeOntImage',to_timestamp('05-FEB-21 08.30.00.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'),'service',null,'mS',null,'SUCCESS',null,'105333602','W1032002968','102706295','IRJ3QXX','j91c','W','N','N','0');

Sign In or Register to comment.