Strange DateTime conversion issues

I have two issues that might be related (not sure, but posting them here as one post, so apologies if that’s incorrect)

My config (SS 5.4, no-prelease code)

JsConfig.Init(new ServiceStack.Text.Config {
                    DateHandler = DateHandler.ISO8601,
                    SkipDateTimeConversion = true,
                    EmitCamelCaseNames = true,
                    ExcludeTypeInfo = true
                });

Issue 1

I’m reading in a source JSON from file with a property that is a short date, e.g. “yyyy-MM-dd”, mapping it to an object and returning a list of those json objects…

The 8601 conversion behaves strangely in regards to the timezone offset, some show -05:00, some entries show -06:00 e.g.

2018-10-23T00:00:00.0000000-05:00 (source JSON = “2018-10-23”)
2015-01-23T00:00:00.0000000-06:00 (source JSON = “2015-01-23”)

Issue 2

I’m also using this particular field in an AutoQuery (Data) search:

/search?MyDateBetween=11/10/2018,12/9/2018

Between included some dates in October (2018-10-23T00:00:00.0000000-05:00 for instance).

If I use Before and After, it works fine…
/search?MyDateAfter=11/10/2018&MyDateBefore=12/9/2018

Am I misusing Between, or is the first issue affecting Between as well somehow?

Firstly you should only ever use YYYY-MM-DD for specifying short date formats.

When parsing Date it has to be parsed into either DateTimeKind.Utc, DateTimeKind.Local or DateTimeKind.Unspecified. By default ServiceStack.Text assumes UTC on the wire and converts it into LocalTime but when using SkipDateTimeConversion it will skip the conversion and assume the Short date is in local time so

"\"2018-10-23\"".FromJson<DateTime>() == new DateTime(2018,10,23) //DateTimeKind.Local
"\"2015-01-23\"".FromJson<DateTime>() == new DateTime(2015,01,23) //DateTimeKind.Local

The TimeZone when serializing into a DateFormat that includes the TimeZone is the local timezone of the computer serializing the date. So if you have 2 different serialized TimeZone’s they’re potentially being serialized with computers in different TimeZones or perhaps it’s a result of Daylight savings. But the TimeZone isn’t contained in the DateTime, if it serializes the TimeZone its using the local TimeZone.

I’d only recommend serializing DateTime in UTC on the wire so it’s always serialized with the same time and :0000 timezone irrespective what local TimeZone the pc is in, by using SkipDateTimeConversion you’re skipping converting Date’s to UTC.

These issues aren’t related, the difference between MyDateBetween is that it uses BETWEEN / AND SQL syntax which is not recommended for Dates as the behavior varies between different RDBMS’s and SQL Server has some gotchas.

The recommendation for RDBMS is to always use WHERE DateField >= @p1 AND DateField < @p2 syntax which in AutoQuery you could use:

/search?MyDateOnOrAfter=2018-10-11&MyDateBefore=2018-09-12

Or you could use From/To pair:

/search?FromMyDate=2018-10-11&ToMyDate=2018-09-12

Or a combination of any, e.g:

/search?FromMyDate=2018-10-11&MyDateBefore=2018-09-12
1 Like
  • Using YYYY-MM-DD solved my issue with Between. FWIW I was using AutoQuery Data, not RDBMS. Is Between safe to use with In Memory Datasets?

  • Regarding the timezone offset issue, the results were from my developer workstation, so it’s just one box in a single call. Changing SkipDateTimeConversion to false doesn’t change the result. The only other thing unique to my config would be that I am converting the overall object type( based on Show POCO names instead of DataMember names). Any other ideas?

I wouldn’t use between for Dates.

The TimeZone info is coming from your PC, i.e that’s the local time zone of your pc.

1 Like

Yep, DST issue, forgot it considers whether DST is applied in those past dates. D’oh! Thanks for the clarity.