1

I need to store intervals represented with their start/end times with precision to 10 minutes.

What are the best practices to do so with CosmosDB? I need to be able have queries like

  • get me all intervals that start at given date
  • get me all intervals that end at given date

ISO representation doesn’t seem to be too suitable for that. I am not sure unix timestamps are either.

Other options include storing day, month, year, hour, minute as ints.

Is there some best practice for it?

PS. General case could involve that i want queries like:

give me intervals that start at the same date as interval with id={id}

or

give me all intervals that are longer than interval with id={id}

dee zg
  • 13,793
  • 10
  • 42
  • 82
  • Why isnt ISO formatted dates not suitable? – James Dec 03 '19 at 02:32
  • @James well, although my main 2 queries are "get all dates", in general i don't like the idea of having dates stored as strings. the lack of natural support for `date` type makes it either impossible or inefficient to do any kind of date-related calculations on cosmos db side. of course we can do UDFs for conversions but having it as some supported type looks more flexible. – dee zg Dec 03 '19 at 08:36
  • unless you intend on doing extraction or diff calculations, ISO 8601 formatted UTC dates would be fine as they would store lexicographically - so range queries like before/after/between etc would work fine. In terms of performance, these fields would be indexed so unless you have a particular use case of poor performance / efficiency then I wouldn't assume that to be the case. – James Dec 03 '19 at 08:48
  • @James you are absolutely correct. for queries with static parameters passed its prefectly fine to use ISO formatted UTC dates as they sure are ok for lexiographic comparison. But, if i tomorrow want to get, for example, `all intervals that are longer than interval with id={intervalid}` then i run into complications. Its just string representation limitation which can be mitigated by using naturally supported types. at least that's my 2 cents. – dee zg Dec 03 '19 at 08:56
  • 1
    if you need to work out the diff, then as per my previous comment, then yes a Unix timestamp would make more sense. However, FWIW you didn't include that as a requirement in your question. – James Dec 03 '19 at 09:04
  • @James fair enough, thanks for pointing that out! didn't say it explicitly. Although, i also didn't say explicitly that `given date` is static parameter neither. It could come from somewhere else. I guess i could make it more clear in original question. I've added clarification now, does it make it more clear now? – dee zg Dec 03 '19 at 09:09
  • I'm not sure why `given date` being static matters? I was never assuming that to be the case anyway. Regardless, in your update, what does `id` represent? – James Dec 03 '19 at 09:16
  • @James it represents any other interval in db. so, effectively, you're reading date from other intervals in db instead of passing it as param. – dee zg Dec 03 '19 at 09:28
  • got you - do the interval dates ever change? Also, what if an interval spans multiple dates? How does that work with your `same date as` query? – James Dec 03 '19 at 09:36
  • @James yes, interval dates can change. Also, they can span multiple dates (if you mean cases where start date and and date are not the same). If you mean cases where intervals have pauses like `from-to-from-to` then - no. But, i don't see it as particularly relevant since its `get intervals that start on the same date as interval with id={id}` (regardless of how long interval is). – dee zg Dec 03 '19 at 09:42
  • so I go back to my previous question, in scenarios where intervals span multiple dates, how do you expect the `same date as id` query? Presumably you'd need to perform some form of date intersection? – James Dec 03 '19 at 10:20
  • @James its same **start** date – dee zg Dec 03 '19 at 10:21

1 Answers1

1

If I'm the guy to deal with this scenario,i would save the date and the intervals as unix timestamp which is similar to _ts system column in cosmos db.

As you know,unix timestamps is a number representing the number of elapsed seconds since January 1, 1970. So that you could use it(calculate) to query the start/end times with precision to 10 minutes.

BTW,the unix timestamps format still could be converted by UDF in cosmos db query,please see this case:Convert TimeScript to Date in Azure CosmosDB SQL Query

Jay Gong
  • 23,163
  • 2
  • 27
  • 32
  • thanks for your reply! yes, unix timestamps are the solution i'm closest to at the moment. let me just ask you one thing: can you think of any real world use case when you would need that particular conversion done in UDF instead on the application side? – dee zg Dec 03 '19 at 08:39