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 :


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.


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.

  • YDM isn't a date format anywhere AFAIK. How is your application using that? Apr 20 '18 at 15:42
  • The error shows what the application sends to PostgreSQL so you may need to fix it (there was maybe a swap between %m and %d or equivalent). Also you can set the datestyle when you connect to your database, using the SET statement. See postgresql.org/docs/10/static/runtime-config-client.html and read about the possible values: DMY, MDY or YMD. Apr 21 '18 at 18:11
  • @RichardHuxton, @PatrickMevzek, indeed, as both of you suspected, the problem isn't the OS locale nor the postgresql. It's the app's fault ! It was suggested to verify the configurations on my side. So I open this issue on stackexchange because most of the date/time field value out of range errors are provoked by a faulty date inputs, such as 2018/02/29. Finally, that was the case also with the application I've been working with. Conclusion : the OS locale and the postgreSQL configuration are probably never the case for these sorts of issues.
    – zer0mode
    Apr 23 '18 at 9:18

Your Answer

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

Browse other questions tagged or ask your own question.