Query in sql on db2 database

Question

I'm using this query:

select substr("Message_Time",6,2) || '/' || substr("Message_Time",4,2) || '/' || '20'|| substr("Message_Time",2,2) || substr("Message_Time",8,2) || ':' || substr("Message_Time",10,2) as "Date",
       count(*) as "Fault", "Message_Location", "Service_Name_U", "Operation_Name_U", "Port_Namespace_U", 
      "Error_Code_U", "Error_SubCode_U", "Fault_Code_U", "Fault_String_U",
       "Requester_Identity", "Application_ServerName_U"
from "Fault_Log_Table_610"
where "Message_Time" >= 1181016220000000 and "Message_Time" < 1181017220000000 and
      "Operation_Name_U" = 'getDomandeDisabile'
group by substr("Message_Time", 6, 2) || '/' || substr("Message_Time", 4, 2)  || '/' || '20'|| substr("Message_Time", 2, 2) || substr("Message_Time",8,2) || ':' || substr("Message_Time",10,2), 
       "Service_Name_U", "Operation_Name_U", "Error_Code_U", "Error_SubCode_U", "Message_Location", "Fault_Code_U", "Fault_String_U",
       "Port_Namespace_U", "Requester_Identity", "Application_ServerName_U"

I need to add 2 hours to Date field, in other words I need to add the number 2 to substr("Message_Time",8,2).

The date has the format 1181020164532000 where:

first number is century then following two numbers are the years then following two numbers are the months then following two numbers are the days then following two numbers are the hours then following two numbers are the minutes and last three numbers are milliseconds

Answer 1

I would pre-compute the timestamp parsing and then add the 2 hours in a CTE (Common Table Expression).

Then I would use this ready-to-use data in the query you want. Something like:

with x as (
select
    timestampadd(8, 2, -- add 2 hours
      timestamp_format( -- parse the VARCHAR into a TIMESTAMP
                       '20' || substr("Message_Time",2,15) || '000',
                       'YYYYMMDDHHMISSNNNNNN')
    ) as "Date",
    "Message_Location", "Service_Name_U", 
    "Operation_Name_U", "Port_Namespace_U", 
    "Error_Code_U", "Error_SubCode_U", "Fault_Code_U", "Fault_String_U",
    "Requester_Identity", "Application_ServerName_U"
from "Fault_Log_Table_610"
where "Message_Time" >= 1181016220000000
  and "Message_Time" < 1181017220000000
  and "Operation_Name_U" = 'getDomandeDisabile'
)
select
    "Date",
    count(*) as "Fault",
    "Message_Location", "Service_Name_U", 
    "Operation_Name_U", "Port_Namespace_U", 
    "Error_Code_U", "Error_SubCode_U", "Fault_Code_U", "Fault_String_U",
    "Requester_Identity", "Application_ServerName_U"
from x
group by "Date", 
    "Service_Name_U", "Operation_Name_U", "Error_Code_U", "Error_SubCode_U",
    "Message_Location", "Fault_Code_U", "Fault_String_U",
    "Port_Namespace_U", "Requester_Identity", "Application_ServerName_U"