Getting Current hour in local time zone ESQL Message broker

Question

I am trying to get the current Hour in local timezone in esql for a requirement by the command:

set throttle_time = EXTRACT(HOUR FROM LOCAL_TIMEZONE);

But it gives me an exception which is given as below:

ExceptionList
RecoverableException
        File:CHARACTER:F:\build\slot1\S000_P\src\DataFlowEngine\MessageServices\ImbDataFlowNode.cpp
        Line:INTEGER:1153
        Function:CHARACTER:ImbDataFlowNode::createExceptionList
        Type:CHARACTER:ComIbmComputeNode
        Name:CHARACTER:SiebelTOSPOSRequestHandler#FCMComposite_1_7
        Label:CHARACTER:SiebelTOSPOSRequestHandler.Compute1
        Catalog:CHARACTER:BIPmsgs
        Severity:INTEGER:3
        Number:INTEGER:2230
        Text:CHARACTER:Node throwing exception
        RecoverableException
                File:CHARACTER:F:\build\slot1\S000_P\src\DataFlowEngine\ImbRdl\ImbRdlStatementGroup.cpp
                Line:INTEGER:666
                Function:CHARACTER:SqlStatementGroup::execute
                Type:CHARACTER:
                Name:CHARACTER:
                Label:CHARACTER:
                Catalog:CHARACTER:BIPmsgs
                Severity:INTEGER:3
                Number:INTEGER:2488
                Text:CHARACTER:Error detected, rethrowing
                Insert
                        Type:INTEGER:5
                        Text:CHARACTER:.SiebelTOSPOSRequestHandler_counternode.Main
                Insert
                        Type:INTEGER:5
                        Text:CHARACTER:6.3
                Insert
                        Type:INTEGER:5
                        Text:CHARACTER:SET throttle_time = CAST(EXTRACT(HOUR FROM LOCAL_TIMEZONE) AS CHARACTER);
                RecoverableException
                        File:CHARACTER:F:\build\slot1\S000_P\src\DataFlowEngine\ImbRdl\ImbRdlDateTimeFunctions.cpp
                        Line:INTEGER:503
                        Function:CHARACTER:ExtractFnCall::extractHour
                        Type:CHARACTER:
                        Name:CHARACTER:
                        Label:CHARACTER:
                        Catalog:CHARACTER:BIPmsgs
                        Severity:INTEGER:3
                        Number:INTEGER:2477
                        Text:CHARACTER:Cannot extract %3 field from value %4
                        Insert
                                Type:INTEGER:5
                                Text:CHARACTER:.SiebelTOSPOSRequestHandler_counternode.Main
                        Insert
                                Type:INTEGER:5
                                Text:CHARACTER:6.28
                        Insert
                                Type:INTEGER:5
                                Text:CHARACTER:HOUR
                        Insert
                                Type:INTEGER:5
                                Text:CHARACTER:INTERVAL '360' MINUTE

Please if anybody could help me, I would be thankful. Regards

Answer 1

You say you want "the current Hour in local timezone".

LOCAL_TIMEZONE does not return anything containing what you want. Carefully read this article for an explanation. Also, as a simple experiment, run "SET Environment.temp = LOCAL_TIMEZONE;" then look at what the resulting value and data type of Environment.temp is.

Instead, use CURRENT_TIMESTAMP.