database - Azure-DocumentDb: Top1 in Group BY Query -
i using documentdb store user's data analytics purpose. in collection, have 10-15 rows per day per user respective timestamps in utc.
i want write query show data admin, select last record each user each , every day, adjustment of admin's timezone.
anu suggestions on, how should storing records make query faster or suggestions on how should form query ?
here 1 approach has worked me in past mongodb , i'm in process of implementing documentdb:
first, store _validfrom , _validto field every row. when create first row user, _validfrom creation time. make sure of timestamps in zulu (gmt) time (e.g. "2015-08-12t12:34:56.789z". _validto set value means positive infinity. use string "9999-01-01t01:01:01.000", can use maxint if storing dates integers. when second , subsequent records, update _validto prior record equal set _validfrom new record. new record has _validto representing positive infinity. yes, means every update involves 1 read , 2 writes. use stored procedure atomic.
then need way shift end of day admin's timezone zulu time. created time object in npmjs packages tztime , lumenize (which includes tztime) purpose. end of day on 2015-08-11,
new time('2015-08-11').getisostringintz('america/denver')
returns2015-08-11t06:00:00.000z
indicating denver 6 hours shifted gmt. tztime/lumenize contains olson timezone files can shift point in history. joda time similar java. not sure .net. if need create set of such timestamps last 30 days, tztime/lumenize include timeline object, allows specify time range, timezone, , allows knock out weekends , holidays. return list of such timestamps.lastly, need submit query return last record each user on date.
_validto > [tzshiftedendofday] , _validfrom <= [tzshiftedendofday]
here slide deck explains approach in greater detail starting @ slide 10. here post explains philosophy timezone precise programming. also, i've moved of lumenize documentdb stored procedures , i'm considering moving more. lumenize timeseriescalculator of in stored procedure on documentdb. it's next thing on list port documentdb. let me know if you'd use , i'll prioritize , let know when it's done.
Comments
Post a Comment