DateTime timestamp is changing when saving

Question

I have two types of dates that are set programmatically.

initialDate = 06/23/2020 00:00:00

finalDate = 06/23/2020 23:59:59

These dates are set programmatically like:

var initialDate = DateTime.UtcNow.Date.AddDays(_ff).Add(startTimeSpan);
var finalDate = DateTime.UtcNow.Date.AddDays(_ff).Add(endTimeSpan)

When saving this to db, the timestamp is changed.

The dates become like this:

initialDate = 06/23/2020 04:00:00.000
finalDate = 06/24/2020 03:59:59.999

How can I prevent this from happening in C#?

Tip: Don't just put `ES db` without explaining what it is -- people will be hard pressed in guessing what is stands for. In this case, I am going to assume it's `Elasticsearch` though not too sure. Whatever the case, have you checked your DB's timezone setting? I see the UTC timestamp getting adjusted to a GMT+4 timezone. — WGS, Jun 24 '20 at 06:32
I want it to take only the datime I am passing. How can I achieve that? — CadaMerx, Jun 24 '20 at 06:35
Yes it was helpful. But I ignored the timezone using DateOffset — CadaMerx, Jun 26 '20 at 13:25
Fantastic. You can add that as an answer or update your question with the findings. It ll be helpful to someone. Hope you don't mind to approve my answer if it's helpful. — Gibbs, Jun 26 '20 at 13:33

Gibbs · Accepted Answer · 2020-06-24T07:18:16.860

2

As you mentioned in the comment, your timezone is GMT +4. That's why 4hours are added to your date.

Internally, dates are converted to UTC (if the time-zone is specified) and stored as a long number representing milliseconds-since-the-epoch.

Reference

It's best to keep UTC formats in the index. And apply timezone settings at the query time.

To query the data:

{
  "query": {
    "range": {
      "initialDate": {
        "time_zone": "+04:00",        
        "gte": "2020-01-01T00:00:00", 
          //value you are providing as input from your timezone, hence I am adding +04
        "lte": "now"                  
      }
    }
  }
}

Reference

time_zone (Optional, string) Coordinated Universal Time (UTC) offset or IANA time zone used to convert date values in the query to UTC.

EDIT:

Apart from the internals, you want to see the date that you provide. You can do that by using timezone format in the mapping.

yyyy-MM-dd'T'HH:mm:ss.SSSZ

Mapping: - PUT http://host:port/indexName/_mapping

timestamp1{
"type": "date_nanos",
"ignore_malformed": true,
"format": "yyyy-MM-dd'T'HH:mm:ss'Z'"
}

Post: POST http://host:port/indexName/_doc

{
    "timestamp1":"2020-12-31T16:00:00Z",
    "key":"1"
}

Search: GET http://host:port/indexName/_search

 "_source": {
        "timestamp1": "2020-12-31T16:00:00Z",
        "key": "1"
    }

But internally this date will be stored as milliseconds and retrieval time it uses the string you provided at the time of index.

More format options

edited Jun 24 '20 at 07:18

answered Jun 24 '20 at 06:48

Gibbs

21,904
13
74
138

What modifications do I need to make? – CadaMerx Jun 24 '20 at 06:49
No. but why doesn't it save the datetime that I am providing? Also, display same datetime on Kibana. – CadaMerx Jun 24 '20 at 06:56
I am not understanding where do I set the Mapping, timestamp1 ? – CadaMerx Jun 24 '20 at 07:17
I set that on my test index. [Refer](https://www.elastic.co/guide/en/elasticsearch/reference/current/mapping.html) - added syntax too – Gibbs Jun 24 '20 at 07:19
But it my case, I have an object in C#. How do I do that? The mapping takes place from my class. – CadaMerx Jun 24 '20 at 07:20
You can't do that via `C#`. I suggest you to understand `elasticsearch`. `c#` is a client to access data stored in `elasticsearch`. – Gibbs Jun 24 '20 at 07:21
But I am storing the datetime from c#. I want it to save the exact datetime in ES. – CadaMerx Jun 24 '20 at 07:25
Can you show me the mapping of `initialDate` by executing `http://host:port/indexName/_mapping`. I can help if there is a way with this. – Gibbs Jun 24 '20 at 07:26
`"initialDate":{"type":"date"},"finalDate":{"type":"date"},` Here you are. – CadaMerx Jun 24 '20 at 07:30
You need to pass the date as the way I mentioned in the answer. It will work from c#. `yyyy-MM-dd'T'HH:mm:ss.SSSZ` – Gibbs Jun 24 '20 at 07:34
How can I modify that from c# ? – CadaMerx Jun 24 '20 at 07:39
[Refer](https://stackoverflow.com/questions/1728404/date-format-yyyy-mm-ddthhmmssz) It will help you – Gibbs Jun 24 '20 at 07:40

DateTime timestamp is changing when saving

1 Answers1