-1

Is it possible to create a new item and set a field to the current time - as set on the server, in Cosmos DB?
I'm looking for something similar to INSERT INTO t VALUES (NOW()); in SQL.

I'd like to avoid asserting that the client time is correctly synced.

Update: To clarify, I don't mean GetCurrentDateTime(). I want to set the value of a custom field to the current server time.
e.g. {"started": "<NOW>"}

Can anyone share the code (in C# or Python, for example) that can be used?

Eli Finkel
  • 463
  • 2
  • 13
  • I don't understand your edit. The only timestamp that gets autogenerated is `_ts` (and that changes, every time you update a document). If you want to store a timestamp, you have to call a function like `GetCurrentDateTime()` to retrieve the current timestamp, according to Cosmos DB, and then add that value to your document. As far as `INSERT` goes... there is no such thing in Cosmos DB's SQL dialect; there is only `SELECT`. So it's impossible to do a single inline "grab date and write to database" via SQL. – David Makogon Feb 16 '23 at 21:52
  • OK. That means I'd have to do 2 queries, 1 to get the time and another to store it. It would be nice if that can be avoided. As I explained, I would like to avoid using the SDK based time function. I guess I could cache the server time and add a monotonic time diff. Still, I think the full answer has value and isn't a duplicate, but whatever.. Thanks – Eli Finkel Feb 17 '23 at 05:36

1 Answers1

0

A call to GetCurrentDateTime() will return current timestamp, which you can then store in a property of your new document. You would retrieve this via a SELECT. You'd then have to use a language-specific SDK call to write your document to your collection. Note that there is no INSERT statement in Cosmos DB's SQL dialect - it has to be done via SDK (or low-level API) call.

Also note that _ts will be automatically set to the exact time (in clock ticks) that the document is saved/updated. This will be the exact time the document was written. You could then save this value somewhere, upon first creating your document, to preserve original creation timestamp.

See here for more info on all date/time functions.

David Makogon
  • 69,407
  • 21
  • 141
  • 189
  • Thanks. This isn't similar to the insert scenario though and doesn't address the question. – Eli Finkel Feb 16 '23 at 20:56
  • You can retrieve Cosmos DB's time with `GetCurrentDateTime()` and then store that in your doc, followed by a write of the doc. Note that there is no such thing as `INSERT` in Cosmos DB's SQL dialect, so you either have to call `GetCurrentDateTime()` via `SELECT`, then storing that value in your doc, or call an SDK-based date-time function to get current timestamp, and write it to your document. You then have to call a language-specific SDK call to save your document. – David Makogon Feb 16 '23 at 21:50