The PowerQuery recently introduced a new set of syntax for time format
strftime(facet) - GMT iso8601 output
strftime(facet, pattern) - GMT strftime pattern support
strftime(facet, pattern, timzeone) - offsetting with a given timezone
simpledateformat - it follows the same arguments as
strftime, but accepts a Java SimpleDateFormat pattern.
strptime(facet, pattern) - parse a String datetime to a nanosecond value
simpledateparse(facet, pattern) - parse a String datetime to a nanosecond value
Here are additional sources for reference on the patterns to use.
To further illustrate how those functions can be applied in powerqueries, I have a sample application that runs every minute and checks for new tickets' most recent updated timestamps.
2023-03-06 16:22:29,669 root INFO New ticket: 7357. Updated at: 2023-03-06 16:22:15
With the new added functions, I can easily get the delta between the application's execution time (2023-03-06 16:22:29) and the ticket's last updated time (2023-03-06 16:22:15). Here is the power query to get that answer
"New ticket" serverHost='tickets_assignment'
| parse ".* Updated at: $ticket_updated_time$"
| let ticket_updated_time_epoch = strptime(ticket_updated_time, "%Y-%m-%d %H:%M:%S")
| let delta_sec = (timestamp - ticket_updated_time_epoch)/1000000000
| columns message, process_run_time = strftime(timestamp, "%Y-%m-%d %H:%M:%S", 'PST'), ticket_updated_time = strftime(ticket_updated_time_epoch, "%Y-%m-%d %H:%M:%S", 'PST'), updated_time_old_syntax.timestamp = ticket_updated_time_epoch, delta_sec
A quick breakdown for the above example
1. The ticket's updated timestamp isn't yet parsed from the raw message, so we use the
parse function to extract the value.
2. Converting the ticket's updated time to an epoch value. We apply
strptime and specify the timestamp's pattern to derive the value.
3. Calculating the delta in seconds by getting the difference between the timestamp of the message and
4. Displaying the columns to show the raw message, the process execution timestamp in PST, the ticket's last updated time in PST, and the delta.
Note that I added one additional column "updated_time_old_syntax.timestamp". This is the prior syntax to get the human readable timestamp by adding
.timestamp to the field's name. You can see that the value is exactly the same as "ticket_updated_time", but the new syntax provides more flexibilities for time patterns.
Please sign in to leave a comment.