PostgreSQL datestyle / OS locale issue

Question

The problem of this issue has been identified and can be treated as resolved. (see the comments bellow)

I try to install a web-application on a server (debian) and on a local machine (ubuntu).

When I log-in the same error reproduces on both machines - odd timestamp interpretation. When a user logs-in certain data is stored in the database and one of those details is date and time.

The error shows a strange thing - looks like the date is getting written in YDM format, which doesn't correspond to the default postgresql datestyle. That's the postgresql error log :

2018-04-18 15:57:34.838 CEST [11031] user@db STATEMENT:  insert into log(login, nom_mod, log_date, comment, ip) values ($1, $2, $3, $4, $5) RETURNING log_id
2018-04-18 15:57:36.995 CEST [11033] user@db ERROR:  date/time field value out of range: "2018-18-04 15:57:36"
2018-04-18 15:57:36.995 CEST [11033] user@db HINT:  Perhaps you need a different "datestyle" setting.

I suspect that the system locale affects the database initialization. I recreated the cluster several times by following those steps like that :

sudo pg_createcluster --locale fr_FR.UTF-8 9.6 main

That created an unexpected 'positive' glitch - I found myself connected after refreshing the page. That's it, case solved ! I disconnected and tried to reconnect. Unfortunately the error reproduced.

This is the date command output on my local system :

vendredi 20 avril 2018, 15:05:53 (UTC+0200)

And the system locale :

LANG=en_US.UTF-8
LANGUAGE=en_US
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC=fr_FR.UTF-8
LC_TIME=fr_FR.UTF-8
LC_COLLATE="en_US.UTF-8"
LC_MONETARY=fr_FR.UTF-8
LC_MESSAGES="en_US.UTF-8"
LC_PAPER=fr_FR.UTF-8
LC_NAME=fr_FR.UTF-8
LC_ADDRESS=fr_FR.UTF-8
LC_TELEPHONE=fr_FR.UTF-8
LC_MEASUREMENT=fr_FR.UTF-8
LC_IDENTIFICATION=fr_FR.UTF-8
LC_ALL=

That's the pertinent section from postgresql.conf :

# - Locale and Formatting -

datestyle = 'iso, dmy'
#intervalstyle = 'postgres'
timezone = 'localtime'
#timezone_abbreviations = 'Default'     # Select the set of available time zone
                                        # abbreviations.  Currently, there are
                                        #   Default
                                        #   Australia (historical usage)
                                        #   India
                                        # You can create your own file in
                                        # share/timezonesets/.
#extra_float_digits = 0                 # min -15, max 3
#client_encoding = sql_ascii            # actually, defaults to database
                                        # encoding

# These settings are initialized by initdb, but they can be changed.
lc_messages = 'en_US.UTF-8'                     # locale for system error message
                                        # strings
lc_monetary = 'fr_FR.UTF-8'                     # locale for monetary formatting
lc_numeric = 'fr_FR.UTF-8'                      # locale for number formatting
lc_time = 'fr_FR.UTF-8'                         # locale for time formatting

# default configuration for text search
default_text_search_config = 'pg_catalog.english'

Remark : I can't alter the database as suggested in this solution :

ALTER DATABASE database_name SET datestyle TO "ISO, YDM";

because that format YDM isn't conform with the postgresql datestyle.

According to the postgresql documentation LC_COLLATE and LC_TYPE can not be modified once a database is created, but that's not problematic since it's possible to recreate the database once those two variables are properly set.

^{https://www.postgresql.org/docs/current/static/locale.html}

However, what's the right configuration ? Which locale variables should be configured and how should they be set ? Or the issue is elsewhere ?

Thanks for pointing out any suggestions about how to sort this out.