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}

Improve this question
12
  • Why isnt ISO formatted dates not suitable? Commented Dec 3, 2019 at 2: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. Commented Dec 3, 2019 at 8: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. Commented Dec 3, 2019 at 8: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. Commented Dec 3, 2019 at 8: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. Commented Dec 3, 2019 at 9:04

1 Answer 1

Reset to default
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

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.