Format 12h format timestamp string to TIME in iDB2

Question

I need a iSeries DB2 SQL query which can transform this timestamp 1/1/1900 12:00:00 AM into 00:00:00 (handling the 12h to 24h time convertion). The scalar functions I've used don't handle this time format, the only way I managed to support this was with the following query:

SELECT (
    CASE 
    WHEN LOCATE('PM', '1/12/1900 12:00:00 AM') = 0 THEN 
    REPLACE(CHAR(TIME(TIMESTAMP_FORMAT('1/12/1900 12:00:00 AM', 'DD/MM/YYYY HH24:MI:SS'))) , '.', ':' )
    ELSE
    REPLACE(CHAR(TIME(TIMESTAMP_FORMAT('1/12/1900 12:00:00 AM', 'DD/MM/YYYY HH24:MI:SS')) + 12 HOURS) , '.', ':' )
    END) 
FROM SYSIBM.SYSDUMMY1;

Is there a better way to achieve the objective ?

EDIT 6/JUN/2014

I've found that it might be possible to set the date format (ISO,USA, ...) in the connection string, which would solve all of the problems I'm currently having with this, but, I haven't found the correct parameter name to achieve that.

Answer 1

Assuming you have specified the correct values on the connection to indicate the correct date and time formats, if you can then convert/source the date and time values separately, you can then use the below query for conversion to your desired format:

select to_char( timestamp( date('12/12/1901') , time('12:00 PM') ),  'YYYY-MM-DD HH24:MI:SS')
from sysibm/sysdummy1;

Answer 2

The following expression should suffice for the purposes expressed by the OP; i.e. for obtaining just the TIME value in the character-string format HH:MM:SS [where HH represents 00 to 24], as derived from string values that are an effective timestamp in the format MM/DD/YYYY HH:MI:SS AM for which the single digit variants of both MM [i.e. just M] and DD [i.e. just D] must be supported. So for example, such that the column-name ts_str is VARCHAR(22):

char( time( insert( right( ts_str, 11 ), 6, 3, '' ) ) 
       + int( left( right( ts_str, 05 ), 2 ) ) seconds
      , JIS ) 

In the above expression, with the right-most eleven characters known to be the time portion in the format HH:MM:SS AM, from which the :SS is truncated by the INSERT scalar, thus leaving a valid *USA time-string value HH:SS AM, that string is converted to a TIME value with the TIME casting scalar. Then the second line picks out the SS value, casting that to an integer with the INT casting scalar, and that value is the duration in SECONDS added to the derived TIME. Finally that adjusted time value [i.e. with seconds added-back] is cast to character using the *JIS format which is HH:MM:SS [where HH is 00 to 24].

Now, about the TIMESTAMP_FORMAT:

The DB2 for IBM i 7.1 has, according to updated documentation IBM i 7.1-> Database-> Reference-> SQL reference-> Built-in functions-> Scalar functions-> TIMESTAMP_FORMAT, apparently with some level of Technology Refresh or cumulative level, there has been added support for the Meridian indicator [i.e. 'AM'] within a format-string. The following expression has been confirmed, albeit on IBM i 7.3, to properly convert the timestamp-like strings from the OP, into a TIMESTAMP:

to_date( ts_str, 'MM/DD/YYYY HH:MI:SS AM')

Thus that use of the TIMESTAMP_FORMAT [shown just above as TO_DATE], given ts_str represents the input values described in the OP, should be able to derive a TIMESTAMP directly from those input timestamp-strings, specifying nothing more than that scalar with a format-string as the second argument and the input-string as the first argument. Quite simple, if available.

Note: I do not know which, neither of a TR nor a cumulative PTF level on v7r1 on which the function becomes available on that release; perhaps look for "What's new as of April 2016" in the IBM i 7.1 Knowledge Center and/or the maintenance levels, both as alluded-to here: TO_CHAR VARCHAR_FORMAT enhancement for numeric editing format-string

The following script includes a setup and then tests two different expressions to verify the results; these are the previously/above noted expressions. This same setup is then used in later examples which show some alternatives if the TIMESTAMP_FORMAT [aka TO_DATE] enhancement is not available [e.g. on older v7r1 maintenance levels, or on v6r1 where the enhancement will not ever be seen], or like on prior releases where there is not even a TIMESTAMP_FORMAT function available, such as for v5r3 on which the latter three scripts were verified:

create table so24067379
( ts_str for ts varchar(26) /* `for ts`-short name ref'd later */
, ts_val timestamp
)
;
insert into so24067379 values
  (  '1/2/1900 12:00:00 AM', '1900-01-02-00.00.00.000000')
, ( '1/12/2012 12:00:00 AM', '2012-01-12-00.00.00.000000')
, ('12/12/1900 12:00:00 AM', '1900-12-12-00.00.00.000000')
, (  '1/2/2012 12:07:00 AM', '2012-01-02-00.07.00.000000')
, ( '7/12/1900 01:07:08 AM', '1900-07-12-01.07.08.000000')
, ( '12/2/2012 01:07:08 AM', '2012-12-02-01.07.08.000000')
, ('12/12/2012 01:07:08 AM', '2012-12-12-01.07.08.000000')
, ( '1/12/1900 12:13:14 PM', '1900-01-12-12.13.14.000000')
, (  '7/2/2012 12:13:14 PM', '2012-07-02-12.13.14.000000')
, ('12/12/1900 12:13:14 PM', '1900-12-12-12.13.14.000000')
, (  '1/2/2012 01:00:00 PM', '2012-01-02-13.00.00.000000')
, ('12/12/1900 01:14:15 PM', '1900-12-12-13.14.15.000000')
, (  '1/2/2012 07:20:21 PM', '2012-01-02-19.20.21.000000')
, ( '1/12/2012 07:20:21 PM', '2012-01-12-19.20.21.000000')
;
create view test0 as
( select m.*
   /* , to_date( ts_str, 'MM/DD/YYYY HH:MI:SS AM') */   
       , TIMESTAMP
          ( date( to_date( ts, 'MM/DD/YYYY' ) )         
          , time( insert( right( ts, 11 ), 6, 3, '' ) ) 
             + int( left( right( ts, 05 ), 2 ) ) seconds
          ) as ts_tst
  from so24067379 as m
)
; -- functional on v6r1 and above per no reference to Meridian enhancement
select * from test0
where ts_val <> ts_tst
; -- above query returns zero rows as verification of adjusted TIME

create view test1 ( ts_str, ts_val, ts_tst )  as
( select m.*                                          
       , to_date( ts_str, 'MM/DD/YYYY HH:MI:SS AM')   
  from so24067379 as m                          
)
; -- requires v7r1 **plus** Meridian enhancement
select * from test1 where ts_val <> ts_tst
; -- above query returns zero rows as verification of TO_DATE

The following two alternatives for which no TO_DATE is utilized are effectively the same, conceptually; each just choosing a different place for the same logic for adjusting the hours to the time. The first is probably prettier, but repeats a larger amount of expressions, because the task is split first, into AM vs PM, before both generating the time-value and then an adjustment to the number of hours, depending on whether the first two digits are '12':

create view test2 ( ts_str, ts_val, ts_tst )  as                
( select                                                        
    m.*  /* , to_date( ts_str, 'MM/DD/YYYY HH:MI:SS AM')  */    
  , (TIMESTAMP                                                  
      ( date( left( ts, locate(' ', ts) - 1 ) )                 
      , case right( ts, 2 )                                     
         when 'PM'                                              
         then time( substr( ts, locate(' ', ts) + 1 , 8 )       
                  ) + case substr( ts, locate(' ', ts) + 1 , 2 )
                        when '12' then 0 else 12 end  HOURS     
         when 'AM'                                              
         then time( substr( ts, locate(' ', ts) + 1 , 8 )       
                  ) - case substr( ts, locate(' ', ts) + 1 , 2 )
                        when '12' then 12 else 0 end  HOURS     
       end                                                      
      )                                                         
    ) as true_ts                                                
  from so24067379 as m
)
;
select * from test2 where ts_val <> ts_tst
-- above query returns zero rows as verification of TO_DATE replacement

This variation takes advantage of the fact that all of the time values will be valid as HH:MM:SS [for HH 01 to 12], despite being incorrect pending adjustment per Meridian specification; that TIME value is calculated first, and then logic is employed to decide on how many hours to adjust that time value. The first test is for the time beginning with '12', for which 'AM' values must have 12 hours subtracted to make 00 hours, but for which 'PM' values no adjustment needs to be made; then for any other 'PM' values [i.e. 01 to 11], 12 hours must added, and finally for any other 'AM' values [i.e. 01 to 11] there are no adjustments to be made:

create view test3 ( ts_str, ts_val, ts_tst )  as                
( select                                                        
    m.*  /* , to_date( ts_str, 'MM/DD/YYYY HH:MI:SS AM')  */    
  , ( TIMESTAMP                                                 
        ( date( left( ts, locate(' ', ts) - 1 ) )               
        , time( substr( ts, locate(' ', ts) + 1 , 8 ) )         
           + case                                               
               when substr( ts, locate(' ', ts) + 1 , 2 ) = '12'
               then case when right( ts, 2 ) = 'AM' then -12    
                         else   0 /* must be 12:xx PM */ end    
               when right( ts, 2 ) = 'PM' then 12               
               else 0                                           
             end       HOURS                                    
        )                                                       
    ) as true_ts                                                
  from so24067379 as m                                          
)                                                               
;
select * from test3 where ts_val <> ts_tst
; -- above query returns zero rows as verification of TO_DATE replacement

While the following is much more succinct, and using the original expression offered to get the TIME in place of what the OP showed was being attempted, I put this example last, because although the expression is nice, the logic is just not very intuitively obvious as might be intuited from reading the prior expressions that used the CASE expressions:

create view test4 as                                    
( select m.*
   /* , to_date( ts_str, 'MM/DD/YYYY HH:MI:SS AM') */   
       , TIMESTAMP
          ( date( left( ts, locate(' ' , ts ) ) )
          , time( insert( right( ts, 11 ), 6, 3, '' ) ) 
             + int( left( right( ts, 05 ), 2 ) ) seconds
          ) as ts_tst
  from so24067379 as m
)
;
select * from test4   
where ts_val <> ts_tst
; -- above query returns zero rows as verification of TO_DATE replacement