TImestamps not in expected timezone

Discussion of open issues, suggestions and bugs regarding database management and development tools for PostgreSQL
Post Reply
montek
Posts: 4
Joined: Mon 06 Jan 2020 23:08

TImestamps not in expected timezone

Post by montek » Mon 06 Jan 2020 23:18

I have a postgres database on Heroku that is set to be on UTC. When I query with TablePlus, I see the timestamps returned in UTC as expected. When I run the same query with dbForget Studio 2019 for PostgreSQL v2.2.207, I get expected results.

Example:

TablePlus: 2020-01-06 21:21:26.454707
dbForge: 2020-01-06 09:21:26.454707

It's clearly the same data, sorta, but dbForge looks to be doing something odd (showing AM/PM but not including that info?).

alexa

Re: TImestamps not in expected timezone

Post by alexa » Tue 07 Jan 2020 14:21

You could try changing this in the 'Data Editor -> Formatting' section of the 'Options' window (select 'Tools -> Options...' from the main menu) having selected 'Timestamp' in the 'Data type' drop-down menu.

montek
Posts: 4
Joined: Mon 06 Jan 2020 23:08

Re: TImestamps not in expected timezone

Post by montek » Tue 07 Jan 2020 15:48

Thank you, alexa.

What would I change it .. to? It appears to already be in the format desired, but it's not including the AM/PM information. I actually don't WANT the AM/PM but want the results in 24-hour time.

I did discover a post where someone suggested adding tt to the end to get the AM/PM. That ... does seem to clarify the results, but as I said before it's still not what I would consider right or comparable to other datetime formats. It should already be showing results in 24-hour time when the tt paramete is missing. Is this is a bug?

What format should I use to get the timestamp in a standard, expected format like ISO 8601--or at least what TablePlus is using?

alexa

Re: TImestamps not in expected timezone

Post by alexa » Tue 07 Jan 2020 18:51

Please perform the following:
1. Select 'Tools -> Options...' from the main menu.
2. Navigate to the 'Data Editor -> Formatting' section.
3. Select 'Timestamp' in the 'Data type' drop-down menu.
4. Replace hh with HH in the 'Edit format' field.

montek
Posts: 4
Joined: Mon 06 Jan 2020 23:08

Re: TImestamps not in expected timezone

Post by montek » Tue 07 Jan 2020 19:18

alexa wrote: Tue 07 Jan 2020 18:51Replace hh with HH in the 'Edit format' field.
Thank you! That is producing expected results.

Let me ask: Shouldn't HH be the default? I never changed this option and it's terribly confusing/misleading for the default timestamp to be .. inaccurate out of the box.

alexa

Re: TImestamps not in expected timezone

Post by alexa » Thu 09 Jan 2020 14:04

Thank you for bringing this to our attention.

We will fix it in one of the next product versions.

Post Reply