0

I am working on a database migration from Oracle 12c to Postgresql 12. We must keep the same date format as the program interacting with this database is written in COBOL and should not be changed.

According to the documentation, set datestyle = 'SQL, YMD' should give me the result I need as the NLS_DATE_FORMAT on the Oracle side is set to 'YYYY/MM/DD HH24:MI:SS'.

enter image description here

The output should be 2020/10/15 15:02:39.710888 UTC as I have set datestyle to YMD.

Other formats seem to work as intended:

enter image description here

enter image description here

enter image description here

Is there a way for me to get the intended Datestyle?

1

I'm not a PostgreSQL guru, but according to PostgreSQL docs:

DateStyle (string)

Sets the display format for date and time values, as well as the rules for interpreting ambiguous date input values. For historical reasons, this variable contains two independent components: the output format specification (ISO, Postgres, SQL, or German) and the input/output specification for year/month/day ordering (DMY, MDY, or YMD). These can be set separately or together. The keywords Euro and European are synonyms for DMY; the keywords US, NonEuro, and NonEuropean are synonyms for MDY. See Section 8.5 for more information. The built-in default is ISO, MDY, but initdb will initialize the configuration file with a setting that corresponds to the behavior of the chosen lc_time locale.

Now, looking at Section 8.5.2 Date/time Output

The output format of the date/time types can be set to one of the four styles ISO 8601, SQL (Ingres), traditional POSTGRES (Unix date format), or German. The default is the ISO format. (The SQL standard requires the use of the ISO 8601 format. The name of the “SQL” output format is a historical accident.) Table 8.14 shows examples of each output style. The output of the date and time types is generally only the date or time part in accordance with the given examples. However, the POSTGRES style outputs date-only values in ISO format.

+---------------------+------------------------+------------------------------+
| Style Specification | Description            | Example                      |
+---------------------+------------------------+------------------------------+
| ISO                 | ISO 8601, SQL standard | 1997-12-17 07:37:16-08       |
+---------------------+------------------------+------------------------------+
| SQL                 | traditional style      | 12/17/1997 07:37:16.00 PST   |
+---------------------+------------------------+------------------------------+
| Postgres            | original style         | Wed Dec 17 07:37:16 1997 PST |
+---------------------+------------------------+------------------------------+
| German              | regional style         | 17.12.1997 07:37:16.00 PST   |
+---------------------+------------------------+------------------------------+

If you want an output format as YMD, you should use ISO instead of SQL.

set datestyle = 'ISO, YMD';
SELECT '20200201'::timestamp;

| timestamp           |
| :------------------ |
| 2020-02-01 00:00:00 |
set datestyle = 'SQL, YMD';
SELECT '20200201'::timestamp;

| timestamp           |
| :------------------ |
| 02/01/2020 00:00:00 |

db<>fiddle here

| improve this answer | |
  • This would imply changing the COBOL programs to user '-' instead of '/' for the separator between year month and date or using to_char in the queries to return correct format. Unfortunately, this is not a luxury I have. – Sebastien Duguay Oct 15 at 15:49
  • I don't think separator character can't be changed. – McNets Oct 15 at 15:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.