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

Store timestamps as TIMESTAMP values. Don't use VARCHAR. You'll run into a lot of issues if you use VARCHAR. — The Impaler
– The Impaler, Commented Oct 18, 2018 at 14:12
So it would decompose as: 1-18-10-20-16-45-32-000? Century 1? — The Impaler
– The Impaler, Commented Oct 18, 2018 at 14:14

The Impaler · Accepted Answer · 2018-10-18 14:33:08Z

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"

answered Oct 18, 2018 at 14:33

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Paul Vernon Over a year ago

or create a VIEW over the table that does the conversion to timestamp if it too much effort to change the column to TIMESTAMP in the first place. You could even make it generated column, or a functional index to help query performance

The Impaler Over a year ago

@PaulVernon I agree. A generated column (persisted) would work quite well for this kind of situation.

Collectives™ on Stack Overflow

Query in sql on db2 database

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related