New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Wrong Timestamp in Postgres #39

Closed
oynek opened this Issue Aug 1, 2016 · 8 comments

Comments

Projects
None yet
3 participants
@oynek
Copy link

oynek commented Aug 1, 2016

[2016-08-01T13:09:52.056Z] INFO: MainApp/10351 on **-MacBook-Air-7.local: Running server on port 9876

But system time is 15:09:52. Checked timezone in postgresql.conf, but already set correctly. Any suggestions?

@Brideau

This comment has been minimized.

Copy link
Owner

Brideau commented Aug 1, 2016

Is that a recent timestamp or one from earlier? It's built to log time in UTC to make it much easier to adapt to different locales (see #12), but if that's a recent timestamp then it's not even correct in UTC.

What timezone is your server in?

@oynek

This comment has been minimized.

Copy link

oynek commented Aug 1, 2016

No, just picked a random one. The latest entry is

[2016-08-01T19:11:27.524Z] INFO: MainApp/10351 on **-MacBook-Air-7.local: Pokemon with ID 17 found.

Server is Europe/Berlin. Am I able to use "correct" timestamps? Would be helpful for the people in my city using it via Tableau online.

@Brideau

This comment has been minimized.

Copy link
Owner

Brideau commented Aug 1, 2016

OK, in that case it's logging the correct time in UTC.

Let me have a quick look. I have an idea for a solution that will work for local areas (it's much more complicated if you have scanners in multiple timezones).

@Brideau

This comment has been minimized.

Copy link
Owner

Brideau commented Aug 1, 2016

@oynek OK, I have a solution sorted, but I'm out of time today to finish implementing it (deadline tomorrow on another project I have to hit). Hopefully you can deal with UTC for now by just subtracting a couple hours from the times listed, and I'll get this wrapped up as soon as possible.

@viperfx

This comment has been minimized.

Copy link

viperfx commented Aug 2, 2016

Regarding timeZones, personally in my experience its best to use UTC/Unix Epoch and then convert to local. I understand the OP is targeting a specific location. Is there a way for Tableau to convert the timezone?

@Brideau

This comment has been minimized.

Copy link
Owner

Brideau commented Aug 2, 2016

@viperfx Tableau - like almost any BI tool - avoids timezones like the plague.

You could do a quick patch where you right click the hidden time timestamp and use one of their functions to add/subtract a few hours from it, but this doesn't get you very far since that timestamp won't be compatible with the date and time dimensions that I built that give you the ability to cut things up however you like. It also wouldn't help if your Tableau license expires and you have to switch platforms.

The true solution is a bit more nuanced, but has much better results. You create two new columns in the main table: one with the timestamp converted to local time, and another with the timestamp converted to an integer. You then creates views of the date and time tables, which are identical to the originals in content, but have different names for their primary key, and different table names. You do this because now when you load everything in Tableau, you can choose to use either the UTC date and time dimensions, or your local ones. Whichever you choose, you drag it in, it joins it automatically, and you're good to go.

@Brideau

This comment has been minimized.

Copy link
Owner

Brideau commented Aug 5, 2016

@oynek @viperfx I know all the data sources are down, but in the meantime, I've added this feature. See the newest patch here and let me know how it goes: https://github.com/Brideau/pokelyzer/wiki/Patches

@Brideau

This comment has been minimized.

Copy link
Owner

Brideau commented Aug 5, 2016

I'm going to close this for now, but feel free to open another ticket if there's any issues with my implementation of time zones.

@Brideau Brideau closed this Aug 5, 2016

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment