Fetching some values on the basis of timestamp values

[former_member1231563]

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

[former_member395650]

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

[Former Member]

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

[Former Member]

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

[Former Member]

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'