datetimeoffset type : invalid input syntax for type timestamp with time zone

[choucavalier]

Hello! First of all, thank you very much for the effort put into this open source project.

I am using https://github.com/tds-fdw/tds_fdw to query SQL Server tables in PostgreSQL. freetds is used as a backend in that context.

When querying tables with a datetimeoffset data type, I'm having an error, which I think is due to the string-conversion of datetimeoffset objects:

# select * from myschema."MyTable" limit 10;
NOTICE:  tds_fdw: Query executed correctly
NOTICE:  tds_fdw: Getting results
ERROR:  invalid input syntax for type timestamp with time zone: "May 31 2022 09:00:23:000AM"

The MyTable table is exposed as a Foreign Table like so:

CREATE FOREIGN TABLE IF NOT EXISTS myschema."MyTable"(
    "MyId" character varying(50) OPTIONS (column_name 'MyId') NOT NULL COLLATE pg_catalog."default",
    "Timestamp" timestamp(3) with time zone OPTIONS (column_name 'Timestamp') NOT NULL,
    "Name" character varying(50) OPTIONS (column_name 'Name') NOT NULL COLLATE pg_catalog."default",
    "Value" character varying(400) OPTIONS (column_name 'Value') NOT NULL COLLATE pg_catalog."default"
)
    SERVER myserver
    OPTIONS (schema_name '_export', table_name 'MyTable');

ALTER FOREIGN TABLE myschema."MyTable"
    OWNER TO postgres;

In the SQL Server database, the table has the following structure

CREATE TABLE [_Export].[MyTable](
  [MyId] [dbo].[shortString] NOT NULL,
  [Timestamp] [dbo].[myDataTimestamp] NOT NULL,
  [Name] [dbo].[shortString] NOT NULL,
  [Value] [dbo].[defaultString] NOT NULL,
 CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED
(
  [MyId] ASC,
  [Name] ASC,
  [Timestamp] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

With the following user-defined data types:

CREATE TYPE [dbo].[myDataTimestamp] FROM [datetimeoffset](3) NOT NULL
CREATE TYPE [dbo].[shortString] FROM [nvarchar](50) NULL
CREATE TYPE [dbo].[defaultString] FROM [nvarchar](400) NULL

Is the datetimeoffset data type correctly supported by freetds?

Is there anything that can be done to make the conversion of the data to correct PostgreSQL timezone-aware datetimes?

Thanks!

[fziglio]

Hi, sorry for late reply. The library is using DB-library. From what I understand (I would need some debug output to make it sure) PG/tds_fdw is doing a select on MSSQL and converting DATETIMEOFFSET into string then trying to convert it back to PG TIMESTAMP. Simply PG is not accepting the string MSSQL/DB-library is using. You can probably change that format using locales.conf, see https://www.freetds.org/userguide/locales.html.

Otherwise you can try a connector using ODBC instead of DB-library.

[choucavalier]

thank you so much @fziglio for your answer

this is exactly our analysis of what is happening as well

here's what our /etc/locales.conf looks like:

[default]
        date format = %b %e %Y %I:%M:%S:%z%p

[en_US]
        date format = %b %e %Y %I:%M:%S:%z%p
        language = us_english
        charset = iso_1

[es_ES]
        date format = %b %d %Y %I:%M%p
        language = spanish
        charset = iso_1

[pt_BR]
        date format = %d/%m/%Y %H:%M
        language = Portuguese
        charset = iso_1

[it_IT]
        date format = %d/%m/%Y %H:%M
        language = Italiano
        charset = iso_1

[fziglio]

The question is also "how pg/tds_fdw expect dates to be formatted?"

[choucavalier]

I think some people have the same issue indeed

[choucavalier]

I think this might be the solution

[fziglio]

I would try also something like %Y-%m-%d %H:%M:%S.%z. Not sure what will happen to the time offset in this case.