trouble with select now()

[c...@cyf.com.ar]

hello, and thanks in advance for your support.
scenario is:
-vbnet visual basic project in windows machine with locality GMT -3
-postgres server running in linux server with GMT 0
-connection with postgres server trough dll ok
-pgadmin open to check results

if I run "select now();" from pgadmin, result is:
2015-03-27 10:00:00 (is gmt 0, as it should be)

if I run same query from my vbnet dll, result is:
2015-03-27 07:00:00 (gmt -3) --> the GMT from my local computer, not the server's GMT.

why?
it's suppose that select now() must return server's time, not local computer's time...
any ideas?
thanks again,

[Francisco Figueiredo Jr.]

I think you stumbled upon an Npgsql problem regarding timezones which are are getting fixed in the current 3.0 development.
When Npgsql gets the value from the database, it is converted to a datetime object which converts the data to the users timezone.

If you can try your code with current master git branch, it would be very welcome so we can check this problem doesn't occur anymore.

For while you will need to use the ToUniverseTime method to get the value in the GMT 0 of your server.  Or you can use the DataReader.GetProviderSpecificValue which will return an NpgsqlDateTime which has more information about the time and date and could be easier to work with.

Sorry for this problem. .net DateTime semantics isn't very easy. If you check our issues section you will find a lot of discussion and patches about DateTime handling :)

I hope it helps.

[Shay Rojansky]

One more addition to what Francisco wrote... Can you please explain exactly how you're accessing the query result? Is it simple an ExecuteScalar()?

--
You received this message because you are subscribed to the Google Groups "Npgsql Help" group.
To unsubscribe from this group and stop receiving emails from it, send an email to npgsql-help...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/npgsql-help/f629a24c-956c-4abc-987b-617b8b9941e1%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Shay Rojansky]

One more thing: I just ran a quick test with v3 and select now() is properly returned as a UTC DateTime, as it should be. So as Francisco said, this seems to have been a weirdness/bug in 2.2.

Claudio, one small comment... You said that "select now() must return server's time, not local computer's time". This is correct, but to be absolutely precise it's important to say that the server's timezone setting isn't returned and doesn't impact the result in any way. The now() function returns a "timestamp with time zone" value, which contrary to its name does not contain a time zone (there has been so much confusion about this). All it returns is a timestamp in UTC.

Unfortunately I don't think we'll be fixing this in Npgsql 2.2... As a workaround you can modify the timestamp in SQL (look at http://www.postgresql.org/docs/9.4/static/functions-datetime.html, http://stackoverflow.com/questions/16609724/using-current-time-in-utc-as-default-value-in-postgresql). Let us know if you need more help.

[c...@cyf.com.ar]

thanks all for your comments and support.
finally solved with non elegant solution, but works.....
select now()::text
converting the timestamp to text value, returns exactly the server's response. then, I can manage the result as I want,

[Shay Rojansky]

Great.

See also parallel discussion on this exact issue: https://github.com/npgsql/npgsql/issues/560.

Feel free to comment there as well.

To view this discussion on the web visit https://groups.google.com/d/msgid/npgsql-help/5e8efb4c-1a49-4864-a745-1511a41b2e70%40googlegroups.com.