DATE type column to correct Timezone or Custom Format

[MichalisDBA]

For example we have a DATE type column that the values are 05-12-2023 14:05:56 but the API returns the following format 2023-12-05T12:05:56.000+00:00

Have an option to return it to the correct timezone 2023-12-05T14:05:56.000+02:00 or even better format it freely how ever you like, yyyy-MM-dd, yyyyMM etc.

2023-12-05 20231205

[thadguidry]

@kdhrubo is the API returning the exact DATE as stored in the DB? Where is the translation of types done? Is it somewhere in here: https://github.com/kdhrubo/db2rest/blob/master/src/main/java/com/homihq/db2rest/core/model/DbColumn.java

We should add some DATEs into our testing.

[thadguidry]

@MichalisDBA My expectation is that DB2Rest does no translation or manipulation of dates as stored in the DB. It should return them as stored. Date String conversions should be done at your application level, or preprocessing client environments (scripts, code, applications, ETL, etc.)

Which DB and Version are you using btw?

[kdhrubo]

@thadguidry currently no translation/formatting is supported with the assumption this will be done by the calling app per their need.

[MichalisDBA]

I tried with Oracle. I will try with MySQL too. If formatting is not possible add an env variable to set the timezone because now the date shows up incorrectly to the API

[kdhrubo]

Date is correct but it's represented in Java date time format by JDBC driver.

Formatting should be possible.

[kdhrubo]

I did the due diligence it should be possible to provide an environment variable for date, datetime and time

https://docs.oracle.com/en/java/javase/17/docs/api/java.base/java/time/format/DateTimeFormatter.html

[kdhrubo]

So the parameter is

USE_DEFAULT_DATETIME_FORMAT - of type boolean. default its set totrue. if you dont set it then the value is `true'

For this row

the result will be:

Now lets set this parameter to false.

There are other parameters if you want to change when not using default date time formats and also for incoming payloads. Will make a release of this as RC2 and start to document and add integration tests. Hope this helps.

Here are the format parameters with default values

TIME_FORMAT:HH:mm:ss DATE_FORMAT:yyyy-MM-dd DATE_TIME_FORMAT:yyyy-MM-dd HH:mm:ss

[thadguidry]

@kdhrubo I disagree with that default of true. We also should change the label and remove DEFAULT from anywhere in a label, so something like ENABLE_DATETIME_FORMATTING. The default value of ENABLE_DATETIME_FORMATTING should be false so that data integrity is a default with DB2Rest as expected. This is very important for data providers since many perform due diligence with data value transforms upon ingress to their DB and perform data quality checks in situ. I.E. their data values are already formatted as they want them to be exposed in an API or application.

You mention the format parameters with default values. I don't understand how ENABLE_DATETIME_FORMATTING would be a Boolean type, and then also be a String type in order to take formatting patterns? Did you forget to mention some other String type parameter you had in mind for users to apply the format pattern, such as DATETIME_FORMAT_PATTERN ?

[kdhrubo]

we can change to ENABLE_DATETIME_FORMATTING a boolean parameter which is by default set to false In that case all input output will be via ISO format. I have listed the 3 other parameters which can be set if the default date-time formatting is not allowed.

Here are the format parameters with default values in case ENABLE_DATETIME_FORMATTING is set to false

TIME_FORMAT:HH:mm:ss 
DATE_FORMAT:yyyy-MM-dd 
DATE_TIME_FORMAT:yyyy-MM-dd HH:mm:ss 

How this going to impact data integrity? this is just formatting of the same data in may ways - for example your name is Thad Guidry but Outlook shows Guidry,Thad so its similar here. The date/time formats follow the JDK date time format allowed and described here

https://docs.oracle.com/en/java/javase/21/docs/api/java.base/java/time/format/DateTimeFormatter.html

[thadguidry]

Not data integrity inside a DB. I'm talking about data VALUE integrity. I.E. we don't change VALUES coming out of a DB without being instructed by a user or system preference setting from them. Changing formats AS A DEFAULT, is a BAD IDEA. Just saying that changing formats should always be an OPT IN thing. (I know all about Java time formatting already - but thanks :-) anyways)

[kdhrubo]

Yes its opt-in with the enable param. hope that helps. And its always set to FALSE so that calling application can take care of changing these ISO dates.

[MichalisDBA]

Hello @kdhrubo How can i test the USE_DEFAULT_DATETIME_FORMAT ?

[kdhrubo]

With version 0.2.7. Available for download

Please check readme page of github

[MichalisDBA]

@kdhrubo Thanks but i don't understand how to define it. I try to run the app with -DENABLE_DATETIME_FORMATTING=true -DDATE_TIME_FORMAT=dd-MM-yyyyHH:mm:ss

Is it documented somewhere?

[kdhrubo]

In version 0.2.7 please use the following:

export USE_DEFAULT_DATETIME_FORMAT=false
export TIME_FORMAT=HH:mm:ss 
export DATE_FORMAT=yyyy-MM-dd 
export DATE_TIME_FORMAT=yyyy-MM-dd HH:mm:ss 

[kdhrubo]

In version 0.2.8, USE_DEFAULT_DATETIME_FORMAT will be replaced with ENABLE_DATETIME_FORMATTING

The actual format parameters will not change.

[MichalisDBA]

Version 0.2.8 works fine with ENABLE_DATETIME_FORMATTING=true and the appropriate TIME_FORMAT, DATE_FORMAT and DATE_TIME_FORMAT.

[kdhrubo]

@MichalisDBA thanks for your confirmation closing this issue.