2013-07-06 14:24:50.200
What i get is the following:
2013-07-06 14:24:50+04
The +04 is obviously the timezone, which is fine, however i loose my precision on the seconds. I have tried most everything and am stumped.
I am currently on still stuck in FME2011.
Thanks in advance for any insight!
According to the Postgresql documentation, the fractional part of Date/Time value does not seem to be output by default (ISO). To make sure whether correct value was inserted, you can use to_char function in a select statement.
Takashi
Takashi is right, this is an issue related to the differences in storage vs. representation of timestamps in the database.
Here is a sample SELECT statement to output a timestamp in the desired format:
select to_char(my_timestamp, 'YYYY-MM-DD HH24:MI:SS.US') as my_timestamp_str
from my_table
David
Thank guys for the response. I have checked and i still seem to be loosing my decimals during the writing/bulk insert part. I have tried to trick FME by setting different data types in write (char, etc) with no luck.
Any other suggestions?
I tested in FME 2013 SP2 and PostgresSQL 9.2, and missing of the fractional part was reproduced when writing a timestamp using the PostgreSQL writer. It could be an FME limitation but I cannot say any more things. To clarify this issue, I recommend you to ask the question to the Safe. In addition, when I inserted a timestamp using an SQLCreator with the following SQL statement, the data was inserted correctly. SQL statements (parameter of SQLCreator): FME_SQL_DELIMITER ; -create table timestamp_test ( my_timestamp timestamp(4) with time zone ); insert into timestamp_test (my_timestamp) values ('2013-07-19 11:26:15.55'); Result shown on the Query Tool of pdAdmin III: 2013-07-19 11:26:15.55+09
Takashi