1

CURRENT_TIME and CURRENT_TIMESTAMP give different results in PostgreSQL. What is the reason of that? Why these are not in same timezone?

select current_timestamp, current_time;

results:

current_time current_timestamp
09:13:30 +0200 2023-08-03 10:13:30.122 +0300

Also my timezone is Europe/Istanbul.

SHOW TIMEZONE;
TimeZone
Europe/Istanbul
Tolga Cakir
  • 725
  • 1
  • 7
  • 13
  • Works for me: https://onecompiler.com/postgresql/3zgeb5vy5 what is your setup? How can we reproduce this problem? – fukanchik Aug 03 '23 at 07:11
  • [Why should I not upload images of code/data/errors?](https://meta.stackoverflow.com/questions/285551/why-should-i-not-upload-images-of-code-data-errors#:~:text=Images%20require%20more%20bandwidth%20than,byte%20and%20every%20millisecond%20count.) – Umut TEKİN Aug 03 '23 at 07:13
  • it does not work for me @fukanchik – Tolga Cakir Aug 03 '23 at 07:22
  • 1
    I removed the images and use txt exports. @UmutTEKİN – Tolga Cakir Aug 03 '23 at 07:23
  • Don't use `current_time`. The documentation warns you about that.Anyway, what do you get for `SHOW timezone;`? – Laurenz Albe Aug 03 '23 at 07:38
  • 2
    The problem might be the Turkish government' s ridiculous timezone policy. There should be a inconsistency between the real time you get and the date information that tzdb provides. Do you have access to operating system? If so, can you share `timedatectl` output and `date` output? If your server is connected a local NTP server that also could be the problem. – Umut TEKİN Aug 03 '23 at 07:50
  • @UmutTEKİN I have a query that used current_timestamp. `select /*...*/ (EXTRACT(EPOCH FROM (current_timestamp - coalesce(d."LastStatusCheck", d."CreatedTime") )) / 60 )::integer as differenceInMinutes /*...*/` This column gives positive value if I execute the query on DBeaver. It gives negative value If I execute with EFCore on Visual Studio debug mode. Is this a bug on EFCore or EFCore PostgreSQL driver? – Tolga Cakir Aug 03 '23 at 09:38
  • @LaurenzAlbe I shared `SHOW timezone` result on question description. – Tolga Cakir Aug 03 '23 at 09:42
  • It works for me with the Turkish time zone. I have got the IANA time zone database version 2023a; what do you have? – Laurenz Albe Aug 03 '23 at 10:36
  • Then it means client based. Do you have direct database server? Did you run `timedatectl` and `date`? Client can show different default behaviours, to be sure try to run these directly on the database server. – Umut TEKİN Aug 03 '23 at 11:38

1 Answers1

-1

In DBeaver you could setup Timezone in Preferences:

https://i.stack.imgur.com/Sr39v.png

Julia Maerkina
  • 1
  • 1