Skip to Content

Fetching some values on the basis of timestamp values

Hi SAP experts,

I am using SAP MII 12.2.

We have ABB SCADA VANTAGE through which we have connected our SAP MII through Plant connector PCo.

In the SCADA table there are columns like name,value,timestamp and UOM.

Now i need to write a select query to fetch values on the basis of timestamp values.

The time stamp field has values in the format like '08/08/2013 12:23:00'.

What my requirement is to fetch values for 24 hours duration between 06:00:00 to 06:00:00 that is  between 6 am on one day to 6 am on the next day.

I am not getting how to frame a SELECT query to achieve this that is how to compare the timestamps.

I am using BETWEEN command but its giving error as we cant comapre the date and time togather,I think some conversion needs to be done.

Any help on this would be highly appreciated.

Thanks,

Praveen

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • avatar image
    Former Member
    Aug 08, 2013 at 12:09 PM

    Hi Praveen,

    To add a pointer to your issue, to compare DateTime, make sure the column which you are getting for DateTime stamp is of the datatype related to DateTime stamp only. If at all, it is of type STRING, then the comparison is not possible either by BETWEEN clause or WHERE clause.

    Best Regards,

    Anuj

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Aug 08, 2013 at 07:52 PM

    Hi Praveen,

    As per my understanding you can use another way around also. What I understand from you question, you need the output based on some shift timing. So for this you may configure the time period from SAP MII menu and pass it to the query.

    Step1: either create a shift or you can modify the existing shift from the MII Menu.

    Step2: now in the workbench, set the "time period" in the "date range" tab within the query.

    If you use time period then you have no need to update the date before querying, it will automatically take the current date and will give the output of the last 24 hrs.

    Hope it will help you.

    Regards,

    Suman

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Aug 08, 2013 at 11:08 AM

    Depending upon the time stamp format that is stored in your table you could use

    SELECT *
    FROM TABLENAME
    WHERE DateTime >= '2013/12/13 06:00:00' AND DateTime <= '2013/12/14 05:59:59'


    Add comment
    10|10000 characters needed characters exceeded

  • Aug 08, 2013 at 11:47 PM

    Hi Praveen,

    If your SCADA system has Oracle DB then you can used below mentioned query. I have used this way in one of my project.

    You can use between also in this command.

    select * from <Table Name>  where   <Table Column Name>  >=  to_date('08/08/2013 23:59:59', 'MM/DD/YYYY HH24:MI:SS')

    Thanks,

    Ritim

    Add comment
    10|10000 characters needed characters exceeded