'datetime' to 'timestamp with time zone' federated field mapping

[donnorman]

Hi,

So on the SQL server (2008) the field is datetime, but in the foreign table definition I made the field timestamptz. This is because I am translating a system from SQL server to Postgres and the new field uses timestamptz.

| SQL                       | Federated                     |
|-------------------------  |----------------------------   |
| 2015-11-02 11:15:29.437   | 2015-11-02 06:15:29.437-05    |
| 2015-11-18 09:32:39.557   | 2015-11-18 04:32:39.557-05    |

Should the federated output have a timezone calculation performed on it?

When I change the foreign table definition to timestamp it does not perform the timezone calculation. I just have to cast the output as timestamptz to get the timezone data, which is fine. I just wasn't expecting it to perform the calculation initially.

Both servers are set up as US/New York.

In my freetds locales.conf file I have the following:

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

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

I'm using tds_fdw version 1.0.6 along with PostgreSQL 9.4 (EDB)

Thanks!

-Don

[GeoffMontee]

Hi @donnorman,

So on the SQL server (2008) the field is datetime, but in the foreign table definition I made the field timestamptz. This is because I am translating a system from SQL server to Postgres and the new field uses timestamptz.

SQL Server's datetime type doesn't have time zones, right? So you are trying to migrate datetime data from a SQL Server datetime column that does not have a time zone component to a PostgreSQL timestamptz column that does have a time zone?

This sounds OK to me. I just want to confirm that I understand.

Should the federated output have a timezone calculation performed on it?

It is normal for PostgreSQL to show time zones when displaying timestamptz data. You might want to keep some things in mind about the timestamptz data type in PostgreSQL:

• The values are always stored in UTC. The original time zone is not stored.
• When it print's the value, PostgreSQL converts it to the time zone identified by the TimeZone configuration parameter.
• If you want to change the output time zone of a given session, use SET TIME ZONE.

There's some more information about time zones here.

Both servers are set up as US/New York.

Your system's time zone is probably set to EST, so that's why PostgreSQL is converting timestamptz data to the time zone offset -05 before displaying.

If you want to treat the data as UTC, then you can do something like this:

postgres=# show TimeZone;
  TimeZone
------------
 US/Eastern
(1 row)

postgres=# SELECT '2015-11-02 06:15:29.437-05'::timestamptz;
        timestamptz
----------------------------
 2015-11-02 06:15:29.437-05
(1 row)

postgres=# SET TIME ZONE UTC;
SET
postgres=# show TimeZone;
 TimeZone
----------
 UTC
(1 row)

postgres=# SELECT '2015-11-02 06:15:29.437-05'::timestamptz;
        timestamptz
----------------------------
 2015-11-02 11:15:29.437+00
(1 row)

When I change the foreign table definition to timestamp it does not perform the timezone calculation. I just have to cast the output as timestamptz to get the timezone data, which is fine. I just wasn't expecting it to perform the calculation initially.

I'm not sure I understand. If the column is defined as timestamp, then it shouldn't have a time zone, so there shouldn't be any time zone data to get. Can you please provide an example?

[donnorman]

@GeoffMontee

Thanks for the very detailed response, I appreciate it!

SQL Server's datetime type doesn't have time zones, right? So you are trying to migrate datetime data from a SQL Server datetime column that does not have a time zone component to a PostgreSQL timestamptz column that does have a time zone?

This sounds OK to me. I just want to confirm that I understand.

Yes, this is all correct.

It is normal for PostgreSQL to show time zones when displaying timestamptz data.

• When it print's the value, PostgreSQL converts it to the time zone identified by the TimeZone configuration parameter.

This is what I am looking for, I guess I just wasn't expecting it to convert the timestamp while querying the foreign table, since like you said the SQL data doesn't have the time zone information.

I'm not sure I understand. If the column is defined as timestamp, then it shouldn't have a time zone, so there shouldn't be any time zone data to get. Can you please provide an example?

Sure, this is a sample of the original foreign table definition:

create foreign table if not exists sample_table (
  created_date timestamptz
) server sqlsrv options (query 'select created_date from sample_table;');

When querying that foreign table, it was applying the time zone conversion for the local system time.

select created_date from sample_table;

The data in SQL is 2015-11-02 11:15:29.437 but the query would return 2015-11-02 06:15:29.437-05.

What I did then was change the definition to this:

create foreign table if not exists sample_table (
  created_date timestamp
) server sqlsrv options (query 'select created_date from sample_table;');

And then queried the field like this:

select cast(created_date as timestamptz) from sample_table;

Which now returns 2015-11-02 11:15:29.437-05. And I believe that is showing me the EST conversion of what would be a UTC timestamp?

[GeoffMontee]

Thanks for the very detailed response, I appreciate it!

No problem! Thanks for using tds_fdw!

Which now returns 2015-11-02 11:15:29.437-05. And I believe that is showing me the EST conversion of what would be a UTC timestamp?

I think this happens because the current session's time zone is used for timestamp data when casting the value to timestamptz. For example, you can see how the exact same statement can return different values depending on the value of TimeZone:

postgres=# show TimeZone;
  TimeZone
------------
 US/Eastern
(1 row)

postgres=# SELECT CAST('2015-11-02 11:15:29.437' AS TIMESTAMPTZ);
        timestamptz
----------------------------
 2015-11-02 11:15:29.437-05
(1 row)

postgres=# SET TIME ZONE UTC;
SET
postgres=# show TimeZone;
 TimeZone
----------
 UTC
(1 row)

postgres=# SELECT CAST('2015-11-02 11:15:29.437' AS TIMESTAMPTZ);
        timestamptz
----------------------------
 2015-11-02 11:15:29.437+00
(1 row)

[sanfx]

that was useful. I faced the same issue and found solution here for my postgresql, new learning .