Feature: Don't auto-convert dates to ISO 8601

[ntorrey]

Is your feature request related to a problem?

Maybe this is related to #1301.

TL;DR
It seems to me that it would be better for the developer to determine the way his data is stored and not have SurrealDB decide that for him/her.

I need to store an array of dates and wish there were a simple way to keep them in the string format I assign them. For example, taking advantage of the CONTENT statement is really convenient for me since I can update data directly from a javascript (stringified) object:

UPDATE thing:id123 CONTENT { 
  dateArray: [
    '2022-10-06', 
    '2022-10-07', 
    '2022-10-08'
  ] 
}

To avoid surrealDB's conversion, I would need to run my object through a string conversion function (or make a more complex query statement) before sending the UPDATE/CONTENT statement, and this would be complicated with nested dates. It would end up looking something like this:

UPDATE thing:id123 CONTENT { 
  dateArray: [
    time::format('2022-10-06', '%Y-%m-%d'), 
    time::format('2022-10-07', '%Y-%m-%d'), 
    time::format('2022-10-08', '%Y-%m-%d')
  ]
}

I guess I'm just trying to figure out a way to reduce complexity, and I'd have to do this a little differently in each case depending on the structure of the data.
In addition, auto-formatting to UTC time complicates things for me because I want the date to be the same regardless of the time zone I'm in. Think of a calendar feature for a school (which is basically what I'm building): If I'm viewing the calendar from a different time zone all of the dates would be shifted unless I process the dates after receiving them which can get a little annoying.

All of this would be much simpler if SurrealDB didn't auto-convert my dates.
If a developer needs the date in UTC 8601 format, it is probably the case that they're already working with those formats in whatever codebase they're using.

Describe the solution

Don't auto-convert the dates! It seems to me that it would be better for the developer to determine the way their data is stored. For example, leave it up to them to cast a string to a datetime if they so desire:

UPDATE test:123 SET date = <datetime> '2022-10-05'

// output
result: [
  id: 'test:123',
  date: '2022-10-05T00:00:00Z'
] 

...and this is a feature that's already built into SurrealDB! (https://surrealdb.com/docs/surrealql/datamodel/casting)

Alternative methods

Doing lots of somersaults in my code 😄 !

SurrealDB version

Nightly release 13 (Oct 4, 2022)

Contact Details

No response

Is there an existing issue for this?

• I have searched the existing issues

Code of Conduct

• I agree to follow this project's Code of Conduct

[Shimori77]

I would like to have this feature too, it's bothersome to have to deal with your data format changing.

It is nice that the way it is now can normalize data across the schema though! So can we have both and can switch which one we prefer?

Or even better, let the developer create a custom converter for the format we desire?

[AntoniosBarotsis]

Perhaps a nice solution would be to have the ability to specify a date format string somewhere which could default to ISO 8601 if left empty.

[ntorrey]

@AntoniosBarotsis Hmm, like in an individual query or as a global setting for the entire database? As a query could be interesting, something like:

UPDATE thing:id123 CONTENT { 
  dateArray: [
    '2022-10-06', 
    '2022-10-07', 
    '2022-10-08'
  ] 
}
DATEFORMAT None 

...although it still seems to me that auto-conversion of dates should be something more like an 'opt-in' feature.

[AntoniosBarotsis]

I feel like per query would be an unnecessary slow down, was think something more like a column specific setting

[ntorrey]

Ah, right. Yes, I suppose in schemafull tables that might even already be possible (I haven't tried it). But that wouldn't work for schemaless tables which is what I'm using.

[oricc]

I believe the best way is to have schemaless tables have no casting whatsoever, while schemafull will cast the columns using it's field definition, this seems to me the most natural way, as it allows for consistency in either table type

[ntorrey]

Woohoo!