Understanding datestyle issues

To set datestyle settings on the server we use datestyle variable.

From PostgreSQL help:

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.

On the client side we must provide date information according to locale settings of client, or in the form of TDateField.DisplayFormat if specified so.

The goal of PostgresDAC is to handle these issues. The main idea is that dates from the server are coming in a textual form, but Delphi TDateField class expects binary TDateTime (double) representation. So to be independent of server datestyle settings PostgresDAC executes such query at the start of each session:

SET DateStyle TO 'ISO, MDY'

It affects only on local session settings and is not visible to other sessions. Now we are absolutely sure what format will be used to represent date values from server.

However, we have a negative aspect. If one decide to execute insert (update) query directly, i.e:

PSQLDatabase1.EXECSQL('INSERT ('2004-06-26 20:31:43') INTO ...')

He will get error message. For such issues we strongly recommend to change session datestyle settings temporarily:

PSQLDatabase1.ExecSQL('SET datestyle TO <....>');
try
   PSQLDatabase1.EXECSQL('INSERT ('2004-06-26 20:31:43') INTO ...')
finally
PSQLDatabase1.ExecSQL('SET datestyle TO ''''ISO, MDY');
end;

Timestamp internals

There is one more problem: PostgreSQL timestamp type allow to store values with microseconds precision (10^-6 sec), but Delphi TDateTime type precision allows only milliseconds (10^-3).

That's why some queries with WHERE clause using timestamps comparison operations may fail. To avoid this you should define date columns as timestamp(0), e.g.:

CREATE TABLE history
(
  his_id integer NOT NULL,
  when timestamp(0) without time zone,
...)