Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Limit Time Ranges In PowerShell for Calculations


By:   |   Last Updated: 2016-03-15   |   Comments   |   Related Tips: More > PowerShell

Problem

We created a check for our application that needs to be run outside of certain hours (or minutes) of the day and wanted to know how we could filter time by either of these options using PowerShell.

Solution

In this tip we'll look at a couple of examples where we apply filters by month, hour, or minute to a script. Let's think about a couple of scenarios where we might consider using a time-based filter in our scripts:

  • The Hong Kong Stock Exchange officially opens its continuous trading at 9:30AM to 12:00PM, then returns after a lunch break from 1:00PM to 4:00PM. If we had a script pulling live data, or trading live, we could use time-based logic to only execute during open hours.
  • Some financial instruments rise and fall relative to the season (or month), and we could use a time range to obtain information on these instruments during seasons - for instance, in energy, pricing analysts and commercial hedgers will sometimes only retrieve information during "selling" seasons because those are good seasons to reduce risk, or make large purchases.
  • We may have periods of times where an event would be a huge concern, and other times, where it might be normal; for instance, a loading schedule may occur over a few hours and thus we expect certain events for those hours, whereas during other hours, we don't expect events.
  • Some API applications will post their information at a certain time and instead of calling a script to obtain the information, we can pull it within a certain time range.
  • Time-based logic is very useful in AI communication to prevent the receiver from becoming annoyed or disrupted.

Let's look at some ways we can obtain this information and wrap logic around it:

[int]$hour = (Get-Date).Hour
Write-Host $hour

In the above example, we get the hour (24 hour based). We would expect to get 14 if it's 2PM and 2 if it's 2AM. In the above example with the Hong Kong exchange, we'd be looking at the hours between 9 to 12, then 13 to 16:

[int]$hour = (Get-Date).Hour

if (($hour -ge 9 -and $hour -le 12) -or ($hour -ge 13 -and $hour -le 16))
{
    Write-Host $hour
    ## Execute-Script
}

What about month?

[int]$month = (Get-Date).Month
Write-Host $month

This returns the month (2) and is based on the 12 month calendar. This can be useful for time-based logic with regards to seasons or months. Suppose that in our trading script we want to check if the current season is still spring and we're using round months based on geography (like March to June):

[int]$month = (Get-Date).Month

if ($month -ge 3 -and $month -le 6)
{
    Write-Host $month
    ## Execute-Script
}

We can also do the same for minute that we did for month and hour:

[int]$month = (Get-Date).Minute
Write-Host $minute

We might discover a consistent minute window in one of our processes or applications where during a certain time range every hour something consistently happens. This could a cause for a concern, but if we learn that its behavior is normal, then we can filter it out with the same approach in logic:

[int]$minute = (Get-Date).Minute

if ($minute -ge 17)
{
    Write-Host $minute
    ## Execute-Script
}

In this case, we'd consistently filter out everything that occurs up to the seventeen minute mark every hour (including the seventeenth minute).

We have other options as well, though these may not be as common in use:

[int]$year = (Get-Date).Year
[int]$month = (Get-Date).Month
[int]$dayofyear = (Get-Date).DayOfYear
[int]$dayofweek = (Get-Date).DayOfWeek
[int]$hour = (Get-Date).Hour
[int]$minute = (Get-Date).Minute
[int]$second = (Get-Date).Second

Write-Host $year
Write-Host $month
Write-Host $dayofyear  ### 365-366 based
Write-Host $dayofweek  ### 0 is Sunday, 6 is Saturday
Write-Host $hour
Write-Host $minute
Write-Host $second
Next Steps
  • Consider anything you do or have running currently that is time based. You can use these, or a combination of these for filters.
  • With ETL and trading especially, you can reduce the amount of resources used by wrapping your scripts with time-based logic if your application doesn't allow for time filters.


Last Updated: 2016-03-15


next webcast button


next tip button



About the author
MSSQLTips author Tim Smith Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools