1

When TimeSpan properties are serialized they end up looking like this: -PT10M, PT30S, or PT6H. My real problem is that in MSSQL I can't use the Cast function to get them to a Time so if there's an easy way to do that I'm open to that solution however I believe the easiest thing will be to figure out how to get ServiceStack to change it's serialization behavior and instead serialize them like this -0:10:00, 0:00:30, or 6:00:00 respectively.

The way I'm serializing them now is to just call MyObject.ToJson().

Is changing the serialization the way to go and if so how can I do that with ServiceStack?

UPDATE

Please ignore the negative time values I'm aware SQL's Time type can only hold positive values. This actually doesn't affect what I'm trying to do at the moment. I have other fields where the time can sometimes be negative so I included them in my question as an example but it's not really relevent to my question.

After some more testing I can confirm that values stored like this like this -0:10:00, 0:00:30, or 6:00:00 deserialize fine. Still, is there a way to force them to serialize to this more easily readable value like this?

Here's almost the same question for Newtonsoft: https://stackoverflow.com/questions/39876232

LorneCash
  • 1,446
  • 2
  • 16
  • 30
  • 2
    `-0:10:00` as a `time` makes no sense; there no such time as 10 minutes past minus 0 o'clock. `time` (in SQL Server) represents a time of the day, not a timespan. SQL Server has no timespan data type. If you want to store a period of time, use a numerical data type where `1` represents the lowest accuracy you need. So, for example, if you need to be accurate to the millisecond, `500` would be `0.5` seconds. – Thom A Jan 19 '22 at 10:42
  • You should never put DTO values directly into SQL, the correct solution is to let the serializer to deserialize it into the appropriate C# Type, e.g. DateTime then add that value into a parameterized query. – mythz Jan 19 '22 at 11:28
  • @Larnu Albert Einstein does not agree ^^ – labilbe Jan 19 '22 at 14:48
  • Albert Einstein wasn't alive when the `time` data type was added to SQL Server, @labilbe . I don't think he has an opinion. – Thom A Jan 19 '22 at 14:50
  • This is relative. – labilbe Jan 19 '22 at 16:01
  • @Larnu `-0:10:00` does make sense if you think of it as a measure of time rather than a time of day. I'm using time relative to an event so if it's after the event the time is positive and if it's before then the time is negative. – LorneCash Jan 19 '22 at 18:34
  • But, as I said, that *isn't* what `time`, in SQL Server, is, @LorneCash . As I stated it represents a time of day, so minus 0 o'clock doesn't make sense. You would say it the date and time is 2022-01-21 -00:00:10 to mean `2022-01-20 23:59:50`. If you want to store a timespan then `time` is the *wrong* choice. – Thom A Jan 19 '22 at 18:47
  • @Larnu I forgot about that... that the range of SQL time type is only 0 to 23:59:59.9999999. I didn't mean to derail my own question with this minor detail. I should have just left that negative value out because it doesn't matter relative to my question. – LorneCash Jan 19 '22 at 19:25
  • @mythz In general I would agree but not in this case. I'm storing the serialized json of an object in SQL so I can easily load that object at a later time by deserializing it. Splitting that single column into dozens of columns across multiple tables would be MUCH more complicated and significantly slow performance. The application works perfect, but now I'm trying to make a report that displays/sorts by values (time) stored in that json. SQL natively supports extracting a single value this with the `Json_Value()' function. – LorneCash Jan 19 '22 at 21:53
  • No one’s suggesting not to blob the object, only that the blob should be deserialized back into a C# type so that its .NET Types are used in parameterized SQL queries, if you want to skip C# then your serialized DTO should already be encoded with encoded values that can be used directly in SQL or if you don’t want to do that serialize values you can easily convert in SQL like the integer value of the TimeSpan, your solution of using C# to serialize to json but not use it do deserialize and instead trying to use the raw serialized JSON value is flawed. – mythz Jan 20 '22 at 00:20

0 Answers0