Shifted timestamp in C/C from PostgreSQL DB and Linux?

Question

I'm using C++ and libpq (PostgreSQL C library) with gcc and PostgreSQL 8.2.19 on Linux. I'm using a column of type "timestamp with time zone". libpq returns query results as C-strings. Sometimes (NOT always) i found the string returned shifted one hour forward (but with the same timezone as others: +02 that is CEST). I verified the problem is directly in the string returned by query (a normal SELECT with the name of the column containing "timestamp with time zone" type) and not subsquent conversions and most execution runs exactly. Is this a sort of (known) bug of Linux and/or PostgreSQL?

EDIT: One time the error occured in following conversions so I tried this code on my platform (obviously including libraries) http://codepad.org/HiBbjX0V Actually, even this is not coherent but the error is the opposite: most time time is shifted forward of one hour and only in the second output the hour is still 11:33:52 like in code. I tried Ubuntu with manual hour setting and hour from Internet. There is the error in both cases. I think the error is at system level but I don't what is.

EDIT: I'm not sure anymore the error is from PostgreSQL too because the row with timestamp is updated twice in quick succession and perhaps erorr is due to first update with hour converted to Linux type as I showed in code.

This is why you don't store local time, people. http://stackoverflow.com/questions/4715620/storing-datetime-utc-vs-storing-datetimeoffset/5264438#5264438 — Ben, Oct 07 '13 at 15:13

score 2 · Answer 1 · answered Oct 08 '13 at 09:24

I made a stupid error due to my scarse experience. As you see in code on codepad I allocated struct tm* data with malloc, initializing with localtime(&now) where now is time_t now = time(NULL) times are processed correctly. I think that without doing this a variable of type struct tm* "doesn't know" the time zone.

Shifted timestamp in C/C from PostgreSQL DB and Linux?

1 Answers1