Forum Discussion
Data source and timestamp
Hi Everyone,
I have been trying to pull some data with an API i have. In this API i use a timestamp to pull the previous months data but when i use this it actually pulls back the previous month, plus the day after. I think this is because i am on British summer time which is one hour ahead of the normal GMT but im not sure. My start of my code for pulling my data currently looks like this:
let
CurrentDate = Date.From(DateTime.LocalNow()),
StartOfLastMonth = Date.StartOfMonth(Date.AddMonths(CurrentDate, -1)),
EndOfLastMonth = Date.StartOfMonth(Date.AddMonths(CurrentDate, 0)),
StartTimestamp = Number.ToText(Duration.TotalSeconds(StartOfLastMonth - #date(1970, 1, 1))),
EndTimestamp = Number.ToText(Duration.TotalSeconds(EndOfLastMonth - #date(1970, 1, 1))),
Url = "https://xxxxxxx/api/v1/xxx/xxxx/xxx/xxxxxxx/xxxxxxxxxxx?start=" & StartTimestamp & "&end=" & EndTimestamp,
.....
I'm not 100% sure this is the correct way to pull the data so if anybody has any advice on how i can fix this so that it only returns the data for august and not also for september that would be great.
Thank you,
Luke
Hello Anonymous ,
1. Instead of calculating the start of the current month, I’ve used Date.EndOfMonth() to get the end of the last day of the previous month.
2. Since timestamps are often based on seconds, I’ve added 86399 (which is the total number of seconds in a day minus one second)
3.Ensure that the API is expecting timestamps in UTC. You may need to convert DateTime.LocalNow() to DateTime.UTCNow() if necessary.
let
CurrentDate = Date.From(DateTime.LocalNow()),
StartOfLastMonth = Date.StartOfMonth(Date.AddMonths(CurrentDate, -1)),
EndOfLastMonth = Date.EndOfMonth(Date.AddMonths(CurrentDate, -1)), // Corrected end of last monthStartTimestamp = Number.ToText(Duration.TotalSeconds(StartOfLastMonth - #date(1970, 1, 1))),
EndTimestamp = Number.ToText(Duration.TotalSeconds(EndOfLastMonth - #date(1970, 1, 1)) + 86399), // End timestamp at 23:59:59Url = "https://xxxxx/api/v1/xxx/xxx/xxx/xxxxx/xxxxxxxxxxxx?start=" & StartTimestamp & "&end=" & EndTimestamp,
If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes are much appreciated!
Thank You
Dharmendar S
2 Replies
Hello Anonymous ,
1. Instead of calculating the start of the current month, I’ve used Date.EndOfMonth() to get the end of the last day of the previous month.
2. Since timestamps are often based on seconds, I’ve added 86399 (which is the total number of seconds in a day minus one second)
3.Ensure that the API is expecting timestamps in UTC. You may need to convert DateTime.LocalNow() to DateTime.UTCNow() if necessary.
let
CurrentDate = Date.From(DateTime.LocalNow()),
StartOfLastMonth = Date.StartOfMonth(Date.AddMonths(CurrentDate, -1)),
EndOfLastMonth = Date.EndOfMonth(Date.AddMonths(CurrentDate, -1)), // Corrected end of last monthStartTimestamp = Number.ToText(Duration.TotalSeconds(StartOfLastMonth - #date(1970, 1, 1))),
EndTimestamp = Number.ToText(Duration.TotalSeconds(EndOfLastMonth - #date(1970, 1, 1)) + 86399), // End timestamp at 23:59:59Url = "https://xxxxx/api/v1/xxx/xxx/xxx/xxxxx/xxxxxxxxxxxx?start=" & StartTimestamp & "&end=" & EndTimestamp,
If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes are much appreciated!
Thank You
Dharmendar S
Hi dharmendars007,
I had to take off the +86399 but using the end of month and UTCnow instead worked great!
Thank you!
Luke