Best way to get GMT seconds of date range

Targets Web
1

What is the best way to calculate the start and end GMT seconds of a date range?

For example, given the date range 1/2/2015 (1420180259) to 11/28/2015 (1448692259) and an interval of 3, I need to calculate the following.

Interval 1: 1/1/2015 00:00:00 to 3/31/2015 23:59:59 = (1420070400 - 1427846399)
Interval 2: 4/1/2015 00:00:00 to 6/30/2015 23:59:59 = (1427846400 - 1435708799)
Interval 3: 7/1/2015 00:00:00 to 9/30/2015 23:59:59 = (1435708800 - 1443657599)
Interval 4: 10/1/2015 00:00:00 to 12/31/2015 23:59:59 = (1443657600 - 1451606399)

2

The first real trouble you will have is knowing the correct GMT offset for those dates (basically is daylight savings in effect or not)
And that will vary depending on date and location since different places have gone on / off daylight time at different times over the years

After that its simple math to subtract the GMT offset so you’re dealing in those times at GMT

What I’m missing is the range given 1/2/2015 to 11/28/2015 doesn’t match the start & end of the result
The result starts earlier and ends later ?

3

Good question. Maybe I’m overthinking what it is I’m trying to accomplish. I just need to calculate the beginning of the month x months back. So I’m thinking to set my range for year-to-date, I would just need to calculate the GMT seconds of 1/1/yyyy. From there, I can use the DateInterval function to calculate the summary intervals. Something like this?

Using Xojo.Core Dim today As New Xojo.Core.Date = Xojo.Core.Date.Now Dim d As New Xojo.Core.Date(today.Year, 1, 1, TimeZone.Current) Dim secondsStart As Int64 = d. SecondsFrom1970
From this use the DateInterval or DateAdd to calculate the end and start of each range?

4

take the date given
subtract that many months
subtract until you get to day 1

5

Thanks for the help. Here is what I came up with in case others need the same.

[code] Dim gmt As New Xojo.Core.TimeZone(0)
Dim dateStart As Xojo.Core.Date
Dim dateEnd As xojo.Core.Date
Dim dInterval As New DateInterval
Dim secondsStart, secondsEnd As Int64

dInterval.Months = interval
dateStart = new Xojo.Core.Date(startDate.Year, startDate.Month, 1, 0, 0, 0, 0, gmt)
dateEnd = dateStart + dInterval

for i As Integer = 1 to intervalCount
secondsStart = dateStart.SecondsFrom1970
secondsEnd = dateEnd.SecondsFrom1970
if accountID > 0 then sqlWhere = "account = " + CStr(accountID)
if Len(sqlWhere) > 0 then sqlWhere = sqlWhere + " AND "
sqlWhere = sqlWhere + "(transaction_date >= " + CStr(secondsStart) + " AND transaction_date < " + CStr(secondsEnd) + “)”
if Len(sqlFilter) > 0 then sqlWhere = sqlWhere + " AND " + sqlFilter
SQL = "SELECT COALESCE(SUM(amount),0) AS amount_total FROM transactions WHERE " + sqlWhere

rs = ExecuteSQLRS(SQL)
...

next[/code]