데이터베이스 질문과 답변

oracle 날짜쿼리 재질문드립니다. 0 5 7,490

by 너구링 [Oracle 기초] oracle 날짜 sysdate dateformat [2018.07.12 10:48:16]

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
SELECT SUBSTR(TB.STAT_10MIN, 1, 4) ||'-'||                 
                SUBSTR(TB.STAT_10MIN, 5, 2) ||'-'||                 
                SUBSTR(TB.STAT_10MIN, 7, 2) ||' '||                 
                SUBSTR(TB.STAT_10MIN, 9, 2) ||':'||                 
                SUBSTR(TB.STAT_10MIN, 11, 2)          AS DSTAT_10MIN  
                ,TA.*                                                  
           FROM                                                    
                (                                                       
                    SELECT   STAT_10MIN            AS  STAT_10MIN                
                            ,GRD_FLAG              AS  GRD_FLAG                  
                            ,WIND_DIR_MAX          AS  WIND_DIR_MAX              
                            ,WIND_SPD_AVG          AS  WIND_SPD_AVG              
                            ,WIND_SPD_MAX          AS  WIND_SPD_MAX              
                            ,TEMP_AIR_AVG          AS  TEMP_AIR_AVG              
                            ,TEMP_AIR_MIN          AS  TEMP_AIR_MIN              
                            ,TEMP_AIR_MAX          AS  TEMP_AIR_MAX              
                            ,TEMP_GRD1_AVG         AS  TEMP_GRD1_AVG             
                            ,TEMP_GRD1_MIN         AS  TEMP_GRD1_MIN             
                            ,TEMP_GRD1_MAX         AS  TEMP_GRD1_MAX             
                            ,TEMP_GRD2_AVG         AS  TEMP_GRD2_AVG             
                            ,TEMP_GRD2_MIN         AS  TEMP_GRD2_MIN             
                            ,TEMP_GRD2_MAX         AS  TEMP_GRD2_MAX             
                            ,HUMIDITY_AVG          AS  HUMIDITY_AVG              
                            ,HUMIDITY_MIN          AS  HUMIDITY_MIN              
                            ,HUMIDITY_MAX          AS  HUMIDITY_MAX              
                            ,TO_CHAR(SIGHTDIST_AVG, '999,999,999')          AS  SIGHTDIST_AVG             
                            ,TO_CHAR(SIGHTDIST_MIN, '999,999,999')          AS  SIGHTDIST_MIN             
                            ,TO_CHAR(SIGHTDIST_MAX, '999,999,999')          AS  SIGHTDIST_MAX             
                            ,CEIL(((GRD1_STS0_CNT+GRD2_STS0_CNT)/240)*100) AS S1 
                            ,CEIL(((GRD1_STS1_CNT+GRD2_STS1_CNT)/240)*100) AS S2 
                            ,CEIL(((GRD1_STS2_CNT+GRD2_STS2_CNT)/240)*100) AS S3 
                            ,CEIL(((GRD1_STS3_CNT+GRD2_STS3_CNT)/240)*100) AS S4 
                            ,CEIL(((GRD1_STS4_CNT+GRD2_STS4_CNT)/240)*100) AS S5 
                            ,CEIL(((GRD1_STS5_CNT+GRD2_STS5_CNT)/240)*100) AS S6 
                            ,CEIL(((GRD1_STS6_CNT+GRD2_STS6_CNT)/240)*100) AS S7 
                            ,CEIL(((GRD1_STS7_CNT+GRD2_STS7_CNT)/240)*100) AS S8 
                            ,CEIL((GRD1_AVG_NACL+GRD2_AVG_NACL)/2) AS S9         
                       FROM FWIS01M T1, FWIS09S T2, FGIS10M T3               
                      WHERE T1.WIS_ID = T2.WIS_ID                           
                        AND T1.WIS_ID = T3.WIS_ID                           
                        AND T1.WIS_ID = #{id, javaType=String ,jdbcType=VARCHAR}                           
                        AND T2.STAT_10MIN <![CDATA[ >= ]]> '201807091500'                 
                        AND T2.STAT_10MIN <![CDATA[ <= ]]> '201807091900'                 
                ) TA,                                                   
                (                                                       
                    SELECT TO_CHAR(TO_DATE('20180709150000', 'YYYYMMDDHH24MISS') +   
                           (LEVEL-1)/24/60*10, 'YYYYMMDDHH24MI') AS STAT_10MIN
                      FROM DUAL                                             
                    CONNECT BY LEVEL <![CDATA[ <= ]]>                                   
                        (TO_DATE('20180709190000', 'YYYYMMDDHH24MISS')             
                    - TO_DATE('20180705015000', 'YYYYMMDDHH24MISS')+1/24)*24*6 
                ) TB                                                    
               WHERE TA.STAT_10MIN(+) = TB.STAT_10MIN                  
               ORDER BY DSTAT_10MIN

 

 

위의 쿼리에서 날짜부분을 어제..예제대로 

 

TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') ,

 

 AND T2.STAT_10MIN  BETWEEN TO_CHAR(sysdate, 'yyyymmdd')
                        AND TO_CHAR(sysdate, 'yyyymmddhh24miss') 

이런식으로 변환해서 해보았는데.. 00시부터~ 현재시간까지의 데이터를 조회해오지낳고..

현재 시분초 데이터만 조회해오는 문제가 있습니다...

조언구합니다...

by 너구링 [2018.07.12 10:49:08]

2018-07-12 10:47                                                                                    
2018-07-12 10:47                                                                                    
2018-07-12 10:47                                                                                    
2018-07-12 10:47                                                                                    
2018-07-12 10:47                    

조회했을때 결과가 이렇게 나옵니다..                                                                 

by 마농 [2018.07.12 11:05:00]

적용하신 TB 서브쿼리만 따로 뽑아서 실행해 보세요.
TB 서브쿼리를 잘못 만드신듯 하네요.

by 너구링 [2018.07.12 12:10:32]

제가 다시 서핑해서...

00시부터 현재까지의 10분단위 데이터를 뽑아보려고  저TB서브쿼리부분을 아예 수정했거든요

?
1
2
3
4
5
6
7
SELECT LEVEL lv
                     , sdt + (LEVEL-1/24)*24*10, 'yyyymmddhh24mi' dt
                  FROM (SELECT TO_CHAR(SYSDATE, 'yyyymmdd')|| '000000' sdt -- 시작일
                             , TO_CHAR(SYSDATE, 'yyyymmddHH24MISS') edt -- 종료일
                          FROM dual
                        )
                 CONNECT BY LEVEL <= (edt - sdt + 1/24)*24*6;

이런식으로 하려하는데...

제 예상과 다르게 

 

1    20180712000230    yyyymmddhh24mi
2    20180712000470    yyyymmddhh24mi
3    20180712000710    yyyymmddhh24mi
4    20180712000950    yyyymmddhh24mi
5    20180712001190    yyyymmddhh24mi
6    20180712001430    yyyymmddhh24mi
7    20180712001670    yyyymmddhh24mi

 

이런식의 데이터가 나오네요... 

혹시 저 쿼리에서 제가 잘못하고있는부분이 어떤부분인지 알수있을까요?

by 마농 [2018.07.12 12:23:00]

날짜 타입과 문자타입을 구별하여 적재적소에 써야 합니다.
혼용하여 부적절하게 사용하시면 안됩니다.
날짜타입이 필요한 곳에 문자타입을 사용하셨네요.
 

?
1
2
3
4
SELECT TO_CHAR(TRUNC(sysdate) + (LEVEL-1)/24/60*10, 'yyyymmddhh24mi') dt
  FROM dual
 CONNECT BY LEVEL <= (sysdate - TRUNC(sysdate))*24*60/10
;

 

by 너구링 [2018.07.12 13:09:32]

많이 배웠습니다..

감사합니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입