How to Filter PI Datalink Data by Time of Day and Weekday: Practical Use Cases and Expressions

Working with OSIsoft PI DataLink in Excel opens up powerful ways to analyze process data with flexible time-based filters. A frequent requirement from PI users is: How can I filter PI DataLink data by specific hours of the day and by specific days of the week, such as only weekdays? This blog post explores straightforward ways to accomplish this, using real-life expressions that you can plug straight into your PI DataLink filter fields.

Scenario: Analyzing Peak Weekday Energy Usage

Let's consider a practical scenario:

“I want to determine the maximum energy used between 1:00 PM and 8:00 PM on weekdays, but only for a certain time period of interest.”

This is a classic energy management question—identifying peak demand periods that often align with business hours.

Leveraging PI DataLink’s Filtering Functions in Excel

The key to this approach is using PI DataLink’s built-in filtering expressions, which can be configured directly within your DataLink ad-hoc displays or reports.

Filtering by Hour of Day

To filter down to specific hours (for example, between 1:00 PM and 8:59 PM), use the Hour('*') function. The hour function returns the hour (in 24-hour format) of a timestamped event. For your case, the hours range from 13 (1:00 PM) up to and including 20 (8:00 PM):

Hour('*') In (13 .. 20)

Or, if your PI DataLink version or syntax requires, you could write:

Hour('*')=13 or hour('*')=14 or hour('*')=15 or hour('*')=16 or hour('*')=17 or hour('*')=18 or hour('*')=19 or hour('*')=20

Filtering by Day of the Week

Suppose you only want data from Monday through Friday. You can use the Weekday('*') function, which returns 1 for Sunday, 2 for Monday, up to 7 for Saturday.

To select only weekdays (Monday–Friday):

Weekday('*') In (2 .. 6)

Combining Date and Time Filters

You can use logical operators like AND (or and, depending on your syntax) to combine these clauses. So, to generate results only from weekday afternoons/evenings:

Hour('*') In (13 .. 20) and Weekday('*') In (2 .. 6)

Add any additional filter for your year or date range—this is easily handled on the main DataLink input for start and end time.

Step-by-step Example

1. Insert PI DataLink query for your data item (e.g., energy tag).
2. Set your start and end time for the period of interest (this can be a date range that covers the entire period you want).
3. In the filter field, enter your filter expression:

   Hour('*') In (13 .. 20) and Weekday('*') In (2 .. 6)
   

4. Calculate your maximum, e.g., using Excel’s MAX function on the returned values.

Advanced: Link to More Examples

For further customization and community use cases, see this similar Community topic, which offers additional samples and ideas for filtering by timestamp.

Conclusion

With PI DataLink’s powerful filtering expressions, you can effortlessly drill into specific hours and days—enabling targeted reporting and better decision-making. Whether you need to spot peak energy use, isolate operational shifts, or compare performance across time, these easy filters replace tedious manual selection with dynamic, repeatable analyses.

Have your own DataLink filtering trick? Share it in the comments!