Archive | DateTime handling RSS for this section

Get-Date

The Get-Date cmdlet is pretty nifty and gives you a lot of date/time formats you’d want to use in your script.

Like if you were to save files with a unique stamp, you’d want a date/time format like <year><month><day><hour><minute><second>

Here are some examples to achieve this goal, with outputs

Get-Date -Format s
2015-11-19T20:22:35

(Get-Date -Format s).Replace("-","").Replace(":","").Replace("T","")
20151119202235


"{0}{1}{2}{3}{4}{5}" -f (Get-Date).Year,(Get-Date).Month,(Get-Date).Day,(Get-Date).Hour,(Get-Date).Minute,(Get-Date).Second
20151119202235

$Date = Get-Date
"{0}{1}{2}{3}{4}{5}" -f $Date.Year,$Date.Month,$Date.Day,$Date.Hour,$Date.Minute,$Date.Second
20151119202235


Get-Date -Format yyyyMMddHHmmss
20151119202235

Get-Date -UFormat %Y%m%d%H%M%S
20151119202235

As you can see, all the given examples have the same output.

The first example doesn’t completely live up to the task as I described in my format, thus I need to remove several characters (by replacing them with a blank), which is the second example.

The third and fourth example will do a format-string and get all different get-date attributes. The fourth example is the same as the third, but in this case I fist put the output of the Get-Date cmdlet into a variable called $Date, this already makes the code a little bit less, but still longer than the Second example.

The fifth and sixth example use the formatting parameters Format and Uformat. Format is the .Net format and UFormat is the Unix format. Those are the shortest ones to use.

I myself like the switches for formatting a lot, for cases where you need to get the date the way you want it, without changing the localization settings of a server/computer.

Like for example in the EU, most (or maybe all) countries work with dates in the <day>-<month>-<year> pattern and time in the 24H format; while in the US dates are worked with in the <month>/<day>/<year> pattern and time in 12h format with AM and PM. But once working with files and sorting dates, the format as used in the example in usually used.

The PowerShell help tells us all the options for Uformat (each is preceded by a % sign):

  • c Date and time – abbreviated (Fri Jun 16 10:31:27 2006)

Date:

  • D Date in mm/dd/yy format (06/14/06)
  • x Date in standard format for locale (09/12/07 for English-US)

Year:

  • C Century (20 for 2006)
  • Y Year in 4-digit format (2006)
  • y Year in 2-digit format (06)
  • G Same as ‘Y’
  • g Same as ‘y’

Month:

  • b Month name – abbreviated (Jan)
  • B Month name – full (January)
  • h Same as ‘b’
  • m Month number (06)

Week:

  • W Week of the year (00-52)
  • V Week of the year (01-53)
  • U Same as ‘W’

Day:

  • a Day of the week – abbreviated name (Mon)
  • A Day of the week – full name (Monday)
  • u Day of the week – number (Monday = 1)
  • d Day of the month – 2 digits (05)
  • e Day of the month – digit preceded by a space ( 5)
  • j Day of the year – (1-366)
  • w Same as ‘u’

Time:

  • p AM or PM
  • r Time in 12-hour format (09:15:36 AM)
  • R Time in 24-hour format – no seconds (17:45)
  • T Time in 24 hour format (17:45:52)
  • X Same as ‘T’
  • Z Time zone offset from Universal Time Coordinate (UTC) (-07)

Hour:

  • H Hour in 24-hour format (17)
  • I Hour in 12 hour format (05)
  • k Same as ‘H’
  • l Same as ‘I’ (Upper-case I = Lower-case L)

Minutes & Seconds:

  • M Minutes (35)
  • S Seconds (05)
  • s Seconds elapsed since January 1, 1970 00:00:00 (1150451174.95705)

Special Characters:

  • n newline character (\n)
  • t Tab character (\t)

The help tells us that the settings for the Format switch can be found here. This is the information which is currently (when writing this blog) available on the site:

Format pattern Associated Property/Description
d ShortDatePattern
D LongDatePattern
f Full date and time (long date and short time)
F FullDateTimePattern (long date and long time)
g General (short date and short time)
G General (short date and long time)
m, M MonthDayPattern
o, O Round-trip date/time pattern; with this format pattern, the formatting or parsing operation always uses the invariant culture
r, R RFC1123Pattern; with this format pattern, the formatting or parsing operation always uses the invariant culture
s SortableDateTimePattern (based on ISO 8601) using local time; with this format pattern, the formatting or parsing operation always uses the invariant culture
t ShortTimePattern
T LongTimePattern
u UniversalSortableDateTimePattern using the format for universal time display; with this format pattern, the formatting or parsing operation always uses the invariant culture
U Full date and time (long date and long time) using universal time
y, Y YearMonthPattern

The following table lists the custom DateTime format patterns and their behavior. For more information, see Custom DateTime Format Strings.

Format pattern Description
d, %d The day of the month. Single-digit days do not have a leading zero. The application specifies “%d” if the format pattern is not combined with other format patterns.
dd The day of the month. Single-digit days have a leading zero.
ddd The abbreviated name of the day of the week, as defined in AbbreviatedDayNames.
dddd The full name of the day of the week, as defined in DayNames.
f, %f The fraction of a second in single-digit precision. The remaining digits are truncated. The application specifies “%f” if the format pattern is not combined with other format patterns.
ff The fraction of a second in double-digit precision. The remaining digits are truncated.
fff The fraction of a second in three-digit precision. The remaining digits are truncated.
ffff The fraction of a second in four-digit precision. The remaining digits are truncated.
fffff The fraction of a second in five-digit precision. The remaining digits are truncated.
ffffff The fraction of a second in six-digit precision. The remaining digits are truncated.
fffffff The fraction of a second in seven-digit precision. The remaining digits are truncated.
F, %F Displays the most significant digit of the seconds fraction. Nothing is displayed if the digit is zero. The application specifies “%F” if the format pattern is not combined with other format patterns.
FF Displays the two most significant digits of the seconds fraction. However, trailing zeros, or two zero digits, are not displayed.
FFF Displays the three most significant digits of the seconds fraction. However, trailing zeros, or three zero digits, are not displayed.
FFFF Displays the four most significant digits of the seconds fraction. However, trailing zeros, or four zero digits, are not displayed.
FFFFF Displays the five most significant digits of the seconds fraction. However, trailing zeros, or five zero digits, are not displayed.
FFFFFF Displays the six most significant digits of the seconds fraction. However, trailing zeros, or six zero digits, are not displayed.
FFFFFFF Displays the seven most significant digits of the seconds fraction. However, trailing zeros, or seven zero digits, are not displayed.
gg The period or era. This pattern is ignored if the date to be formatted does not have an associated period or era string.
h, %h The hour in a 12-hour clock. Single-digit hours do not have a leading zero. The application specifies “%h” if the format pattern is not combined with other format patterns.
hh The hour in a 12-hour clock. Single-digit hours have a leading zero.
H, %H The hour in a 24-hour clock. Single-digit hours do not have a leading zero. The application specifies “%H” if the format pattern is not combined with other format patterns.
HH The hour in a 24-hour clock. Single-digit hours have a leading zero.
K Different values of the Kind property, that is, Local, Utc, or Unspecified.
m, %m The minute. Single-digit minutes do not have a leading zero. The application specifies “%m” if the format pattern is not combined with other format patterns.
mm The minute. Single-digit minutes have a leading zero.
M, %M The numeric month. Single-digit months do not have a leading zero. The application specifies “%M” if the format pattern is not combined with other format patterns.
MM The numeric month. Single-digit months have a leading zero.
MMM The abbreviated name of the month, as defined in AbbreviatedMonthNames.
MMMM The full name of the month, as defined in MonthNames.
s, %s The second. Single-digit seconds do not have a leading zero. The application specifies “%s” if the format pattern is not combined with other format patterns.
ss The second. Single-digit seconds have a leading zero.
t, %t The first character in the AM/PM designator defined in AMDesignator or PMDesignator, if any. The application specifies “%t” if the format pattern is not combined with other format patterns.
tt The AM/PM designator defined in AMDesignator or PMDesignator, if any. Your application should use this format pattern for languages for which it is necessary to maintain the distinction between AM and PM. An example is Japanese, for which the AM and PM designators differ in the second character instead of the first character.
y, %y The year without the century. If the year without the century is less than 10, the year is displayed with no leading zero. The application specifies “%y” if the format pattern is not combined with other format patterns.
yy The year without the century. If the year without the century is less than 10, the year is displayed with a leading zero.
yyy The year in three digits. If the year is less than 100, the year is displayed with a leading zero.
yyyy The year in four or five digits (depending on the calendar used), including the century. Pads with leading zeros to get four digits. Thai Buddhist and Korean calendars have five-digit years. Users selecting the “yyyy” pattern see all five digits without leading zeros for calendars that have five digits. Exception: the Japanese and Taiwan calendars always behave as if “yy” is selected.
yyyyy The year in five digits. Pads with leading zeros to get five digits. Exception: the Japanese and Taiwan calendars always behave as if “yy” is selected.
yyyyyy The year in six digits. Pads with leading zeros to get six digits. Exception: the Japanese and Taiwan calendars always behave as if “yy” is selected. The pattern can be continued with a longer string of “y”s padding with more leading zeros.
z, %z The time zone offset (“+” or “-” followed by the hour only). Single-digit hours do not have a leading zero. For example, Pacific Standard Time is “-8”. The application specifies “%z” if the format pattern is not combined with other format patterns.
zz The time zone offset (“+” or “-” followed by the hour only). Single-digit hours have a leading zero. For example, Pacific Standard Time is “-08”.
zzz The full time zone offset (“+” or “-” followed by the hour and minutes). Single-digit hours and minutes have leading zeros. For example, Pacific Standard Time is “-08:00”.
: The default time separator defined in TimeSeparator.
/ The default date separator defined in DateSeparator.
% c Where c is a format pattern if used alone. To use format pattern “d”, “f”, “F”, “h”, “m”, “s”, “t”, “y”, “z”, “H”, or “M” by itself, the application specifies “%d”, “%f”, “%F”, “%h”, “%m”, “%s”, “%t”, “%y”, “%z”, “%H”, or “%M”.

The “%” character can be omitted if the format pattern is combined with literal characters or other format patterns.

\ c Where c is any character. Displays the character literally. To display the backslash character, the application should use “\\”.

Have fun with getting the date in your format.

Download SolarEdge solar production data and save to csv

I’ve got a nice solar panel setup on my roof, which uploads its data to the SolarEdge monitoring portal (this is the brand of inverter that I got). It appeared that this monitoring portal also has an API to automate getting the energy data. With me working in a company that works on energy savings, monitoring, consultancy etc, it’s a logical step for me to automate downloading the production of my panels, so my collueges at work can import this data in my energy monitoring portal at work and then they can do their magic calculations on them; which results in me getting nice graphs of my solar enrgy production.

The purpose of the tool:

I wrote this tool to be somewhat smart in what to download and what not. I only want to download the information if it’s up-to-date. Next to that, I’d want daily values, but also values with a 15 minute interval. In the end I’d want all the info to be exported to CSV files containing 1 month of data and/or 1 year of data. All files that have already been generated don’t need their data to be downloaded again and overwritten, thus it skips this data once it’s downloaded. In the end I can either mail these files or put them on a file share, so they can be imported by our energy monitoring system. This last step I’ve removed from the script that I share with you.

Several nice ‘techniques’ used in this script to get to the goal:

Since I’m talking with an API, the main important command is the Invoke-WebRequest with a pipe to ConvertFrom-Json

To get the last day of the month, the following line is used: $LastDayOfTheMonth = ((Get-Date -Date “01-$Month-$Year” -Hour 0 -Minute 0 -Second 0).AddMonths(1).AddSeconds(-1)).Day

(which will add a month to the date/time 01-<month>-<year> : 0:00 and then remove 1 second to get to the last second of the previous month, thus resulting in returning the last day of that month (<last day>-<month>-<year> 23:59:59))

In the end, the downloaded data is converted to CSV with this command: | ConvertTo-Csv -Delimiter “;” -NoTypeInformation (in Excel with localization the Netherlands, the semicolon is a better seperator than a comma, since Excel expects a semicolon. This saves me time on opening the csv files and converting the information to an excel readable file. Depending on your localization settings, you’d want to change this accordingly) I also add the switch -NoTypeInformation, since I’m not interested in getting information about the type of variable PS used, I only need the data.

Script parameters and bounds:

The script will check the solaredge portal for the date/time on which the installation received its latest data. If this date is not the same as today, something might be wrong. It’ll warn you about it, but will still download. If it’s out of date for more than 1 day, it’ll give an error about this, but the script will still continue. When no data has been downloaded, the tool will download all data (starting on the installation date of the inverter) per month and save these to csv files. It will also generate files with yearly data. The data that’s downloaded is from the previous month (unless this is a later date than the date the last data was received on the solaredge monitoring portal, then the last data date will be used). In case of the daily values (monthly and yearly csv’s), the tool will only store the value’s if they’re not $null (which is: no data received on portal or the solar panels with its optimizers as a whole aren’t yet initialized by the inverter). In case of the 15 minute interval values, all $null values will be replaced by 0,0 (in that case the inverter is turned off because of its night mode); thus giving a nice list of 96 readings each day, and resulting in about 2688 – 2976 values in each file, depending on the amount of days in the month (which can be checked for, if needed; in my case I don’t, since my work already has many comprehensive tools to check for gaps or strange behaviour in data).

The script can be found and downloaded here: DownloadEnergyData.ps1

 

Edit: I got myself an undocumented API call parameter from a SolarEdge developer, which gives me 15 minute values in WH 🙂 The script has been updated. (&TimeUnit=QUARTER_OF_AN_HOUR)