Inserting a timestamp into a PostgreSQL table

[MattRink]

Hey,

I am having an issue when inserting a TDateTime into a PostgreSQL timestamp without time zone column.

Using a stored procedure I assign the value like so

Code: Select all

FInsertUpdateProc.ParamByName('inShowTime').asDateTime := Now;
FInsertUpdateProc.Execute;

In DBMonitor the in parameter appears as "26/06/2013 11:15:23" but in PostgreSQL the value is "152075-04-23 03:06:08.758956".

Do you know what am I doing wrong?

Edit:

I did a quick test using the following

Code: Select all

CREATE TABLE test (
  id serial NOT NULL,
  timestamptest timestamp without time zone,
  PRIMARY KEY (id));

Code: Select all

program Project2;

{$APPTYPE CONSOLE}

uses
  SysUtils,
  DateUtils,
  Uni,
  UniSQLMonitor,
  DASQLMonitor,
  PostgreSQLUniProvider,
  STD_Database;

var
  Conn : TUniConnection;
  Qry : TUniQuery;
  SQLMonitor : TUniSQLMonitor;

begin
  SQLMonitor := TUniSQLMonitor.Create(nil);
  SQLMonitor.Options := [moDBMonitor];
  SQLMonitor.Active  := true;

  Conn := TUniConnection.Create(nil);
  Conn.ProviderName := 'PostgreSQL';
  Conn.Server       := 'localhost';
  Conn.Port         := 5432;
  Conn.Username     := 'postgres';
  Conn.Password     := 'password';
  Conn.Database     := 'db';
  Conn.Connect;

  Qry := TUniQuery.Create(nil);
  Qry.Connection := Conn;
  Qry.SQL.Add('INSERT INTO Test (TimestampTest) VALUES (:TimestampTest)');
  Qry.Prepare;
  Qry.ParamByName('TimestampTest').AsDateTime := EncodeDateTime(2013, 6, 25, 18, 35, 0, 0);
  Qry.Execute;

  Qry.Free;
  Conn.Disconnect;
  Conn.Free;
end.

and it all worked fine so it is something in my code or in my database schema, but I don't know what. Any pointers would be useful.

Thanks

Edit 2:

I forgot to include my versions so here they are
Windows XP SP3 32bit
Delphi 7 w/ Unidac 5.0.1 for Delphi7
PostgreSQL 9.2.4, compiled by Visual C++ build 1600, 32-bit

I also added additional logging to PostgreSQL and it shows the parameter as
$6 = '152075-04-11 11:22:15.6288'

[MattRink]

Ok, after banging my head against the table for a long time on this I finally solved it after noticing that some correct dates that I was retrieving from the database elsewhere were wrong once the got into Delphi. When looking into that I came across the data type mapping functionality of UniDAC. The following line solved it all...

Code: Select all

Connection.DataTypeMap.AddDBTypeRule(pgTimestamp, ftDateTime);

Simple, really...

PS. Have the Devart guys got a listing somewhere of what is in each unit because I had to search for about 30 minutes just to find out that the pgTimestamp constant is contained in the PgDataTypeMapUni unit.

[Daos]

I could not reproduce the problem. By default, Timestamp fields are converted to DateTime, so the problem and the solutions baffles me.

I looked in the UniDAC documentation. It really has not a list of units and constants for the data type mapping, but I think Devart will add them soon. For the time being to getting constants names and units names for data type mapping you can use the following:
    A unit, that declares constants for data type mapping for a specific database, has name that contains the word *DataTypeMap* and the identifier of the database (for example Pg*, Ora*, My*, etc.). To finding out the exact name of the unit, you can open the folder with the unidac units and find the file with an appropriate name. Once you find out the exact unit name, for example MyDataTypeMapUni, add it to the uses clause of your project and in any procedure write the line

Code: Select all

MyDataTypeMapUni. 

(with a point at the end) and press Ctrl + Space - you will see all constants declared in the unit.

[DemetrionQ]

Hello.

You can use the mentioned Data Type Mapping to solve the problem. If any questions appear, please contact us.