How should timezones be formatted In Matlab, when inserting a string into a postgresql field timestamp with time zone?

Question

I have no problems inserting strings of format 'yyyy-mm-dd hh:mm:ss' into a timestamp with time zone field in my PostgreSQL database using the Matlab datainsert function. However, I can't seem to work out how to include a time zone in the string itself, so Postgres assumes that it has the timezone of my locale.

For example

create table tmp_test (
id serial primary key,
time timestamp with time zone NOT NULL
);

Then, this works:

datainsert(conn, 'tmp_test', {'time'}, {'2016-09-15 03:45:49.326'})

but this doesn't:

>> datainsert(conn, 'tmp_test', {'time'}, {'2016-09-15 03:45:49.326 +00:00'})
Error using database/datainsert (line 301)
Unable to insert element in row 1 column 1, 2016-09-15 03:45:49.326 +00:00. Timestamp format must be yyyy-mm-dd
hh:mm:ss[.fffffffff]

Possible duplicate of [How can i insert timestamp with timezone in postgresql with prepared statement?](http://stackoverflow.com/questions/13676168/how-can-i-insert-timestamp-with-timezone-in-postgresql-with-prepared-statement) — Alex, Sep 16 '16 at 06:26

score 2 · Answer 1 · answered Sep 15 '16 at 05:35

2

I'm assuming Unix(?) is the environment, but the date() command can return a string with the timezone included: i.e., for Eastern Time EDT

 $> TZ='America/New_York' date +%Y-%m-%d_%T_%Z
 2016-09-15_01:34:10_EDT

Was this for a specific language used with PostgreSQL, like PHP, etc?

answered Sep 15 '16 at 05:35

SomeDude

320
2
7

thanks. The environment is actually Windows 7. I am inserting via commands like: `insert into tmp_test(time) values ('2016-08-01 14:22:50.333')`. – Alex Sep 15 '16 at 05:38
Then officially, I refer ya to the specific postgres documentation for date formats: https://www.postgresql.org/docs/current/static/functions-formatting.html – SomeDude Sep 15 '16 at 05:41
thanks, it turns out that this is Matlab specific. I can insert strings with time zones from pgadmin but not in Matlab – Alex Sep 15 '16 at 05:47
I am afraid that the documentation does not give me any insights on how to insert time zones. However, this seems to work: `insert into tmp_test(time) values ('2016-08-01 14:22:50.333 +00:00')` – Alex Sep 16 '16 at 06:22

score 0 · Answer 2 · edited May 23 '17 at 11:51

It looks like the problem is with the Java connector from Matlab. Someone else relying on this had the same problem:

How can i insert timestamp with timezone in postgresql with prepared statement?

Unfortunately this probably means that there is no high level way to get Matlab to do this insert for you, you must rely on low level commands like:

exec(conn, 'insert into tmp_test (time) VALUES (''2016-09-16 16:03:37.313 +00:00'')')

How should timezones be formatted In Matlab, when inserting a string into a postgresql field timestamp with time zone?

2 Answers2