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  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  user@db ERROR: date/time field value out of range: "2018-18-04 15:57:36" 2018-04-18 15:57:36.995 CEST  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_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.
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.