> Cc: "D." , "Eric H. Bowen via gnucash-user"
>
> Subject: Re: [GNC] Working with dates in Postgresql DB
>
> That's exactly what happened a few years ago to a guy from California who did
> some updates while visiting New Zealand.
>
> Regards,
> John Ral
stored
the local date time and then ignored the time zone?
Original Message
From: John Ralls
Sent: Sat May 02 03:53:01 GMT+05:30 2020
To: David H
Cc: "D." , "Eric H. Bowen via gnucash-user"
Subject: Re: [GNC] Working with dates in Postgresql DB
That's e
would enter
those transactions for the tenth. Wouldn't you?
Original Message
From: David H
Sent: Sat May 02 03:46:28 GMT+05:30 2020
To: "D."
Cc: John Ralls , "Eric H. Bowen via gnucash-user"
Subject: Re: [GNC] Working with dates in Postgresql DB
Might be a
store them in UTC, to be
> converted back to some arbitrary time zone at a later time. It makes my head
> hurt, though.
>
> David
>
>
> Original Message
> From: John Ralls
> Sent: Fri May 01 23:30:37 GMT+05:30 2020
> To: "D."
> C
5:30 2020
> To: "D."
> Cc: finf...@gmail.com, "D. via gnucash-user"
> Subject: Re: [GNC] Working with dates in Postgresql DB
>
> David,
>
> You're not thinking it through: It's about 11:00 on Friday 1 May here in
> California but it's 03:00 on Saturday 2
that, and simplify the process piece by
piece. Of course, not being a programmer, I'm just a silly voice in the
wilderness. > > David > > > Original Message >
From: John Ralls > Sent: Fri May 01 10:32:09 GMT+05:30
2020 > To: "D.&
a programmer, I'm just a silly voice in the
> wilderness.
>
> David
>
>
> Original Message
> From: John Ralls
> Sent: Fri May 01 10:32:09 GMT+05:30 2020
> To: "D."
> Cc: "finf...@gmail.com" , "D. via gnucash-user"
>
e
> wilderness.
>
> David
>
>
> Original Message
> From: John Ralls
> Sent: Fri May 01 10:32:09 GMT+05:30 2020
> To: "D."
> Cc: "finf...@gmail.com" , "D. via gnucash-user"
>
> Subject: Re: [GNC] Working wi
Cc: "finf...@gmail.com" , "D. via gnucash-user"
Subject: Re: [GNC] Working with dates in Postgresql DB
David,
I don't know why the decision was made to use time, it was taken long before I
joined the project, but it probably has to do with that being the way computers
keep time, in
ge
> From: John Ralls
> Sent: Fri May 01 00:48:57 GMT+05:30 2020
> To: "finf...@gmail.com"
> Cc: Gnucash Users
> Subject: Re: [GNC] Working with dates in Postgresql DB
>
> GnuCash stores all dates as UTC but displays them as local, applying the
> timezone ru
ry timestamp in a
field that is treated everywhere as a date? What is gained?
David T.
Original Message
From: John Ralls
Sent: Fri May 01 00:48:57 GMT+05:30 2020
To: "finf...@gmail.com"
Cc: Gnucash Users
Subject: Re: [GNC] Working with dates in Postgresql DB
GnuCash
10:59:00 UTC is a very clever fix, or "kludge" as some put it. So
just using the bash shell:
$ date +%F-%Z # Format Strings %F with %Z for time zone
2020-04-30-CDT
This, of course, depends on locale on whatever system you're using.
You could even try:
$ date +%F-TZ=%Z-Week=%U-Day=%u
We show no times at all. The smallest useful division of time in accounting is
a day.
Unfortunately someone decided a long time ago to use unix time to represent
dates, probably because in 1998 that was what was readily available. Naturally
you want to display in local time, otherwise you have
Sorry, I have used a wrong example with 2017-12-31 21:00:00, should be
22:00:00+02 - not summer time.
My SQL conversion of that gives 2018-01-01
Like 22:00 + 2 hours = next day.
Cyprus and British territories here have the same time.
>
> On Apr 30, 2020 at
Curious about this. So is the goal to show all times in UTC on
invoices along with local time? I tend to default most things to UTC
(including local time on computers) and just display in some local
time if necessary. If GMT is available as a time zone, I use that
(GMT is a timezone; UTC is
John, I have found lost 4 transactions, they are from invoices but not
connected with invoices.
Found using your SQL with left outer join and ordering by invoices.date_posted.
They have Null in invoices columns.
They have vendor names In transactions.description...
Found!
GnuCash stores all dates as UTC but displays them as local, applying the
timezone rules for the date, not for today. So in EEST 2020-02-12 22:00:00
displays as 2020-02-13, 2020-06-12 21:00:00 displays as 2020-06-13, but
2020-02-21 21:00:00 displays as 2020-02-21.
Regards,
John Ralls
> On Apr
It is not just adding one day, it depends on the time.
Looks like time 00:00:00 is the same date, not next.
From 21:00:00 is the next date in most cases, but I did not check all
transactions manually =)
How the program converts this wrong dates to the correct ones in its GUI?
I believe I
Hmm, true. Should be always, since you're in a time zone east of the prime
meridian. So you also want to increment the day on those. I think it would be
safest to do it in two queries, the first one being
update transactions post_date = post_date + interval '1 day' where
post_date::TIME !=
If post_date is 2017-12-31 22:00 or 23:00, it means (sometimes?) the real date
is 2018-01-01. At least in cases where I manually checked the invoices.
Setting all times to 10:59:00 will give wrong dates in the program.
Now they are displayed correctly in the program somehow...
I don't think that's necessary.
To fix the wrong times just do an update query, something like
update transactions set post_date::TIME = 10:59:00 where post_date::TIME !=
10:59:00;
I don't know Postgresql's date-time functions well enough to know if that
syntax works, you might have to
How can I help?
I can send you my gnucash file if it helps to find all the bugs.
And how can I change now my wrong dates in transactions?
>
> On Apr 30, 2020 at 20:41, mailto:jra...@ceridwen.us)> wrote:
>
>
>
> Yeah, it's definitely a bug. I easily found the
Yeah, it's definitely a bug. I easily found the wrong code and I'll fix it for
3.903 and 3.11.
The query actually accounts for only 543 of the 547 wrong times, so there's
another error somewhere else.
Regards,
John Ralls
> On Apr 30, 2020, at 10:27 AM, Finfort wrote:
>
> Also I tried to
Also I tried to unpost and post again. No changes.
>
> On Apr 30, 2020 at 19:44, mailto:finf...@gmail.com)> wrote:
>
>
>
> Hi John, The result is: 22:00:00 253 00:00:00 18 21:00:00 250 23:00:00 22 So
> wrong dates are only when I use invoices. On 29/04/2020
I also unzipped GNUCASH file to check the date format and values inside - it
is the same timestamp as in Postgres
>
> On Apr 30, 2020 at 19:44, mailto:finf...@gmail.com)> wrote:
>
>
>
> Hi John, The result is: 22:00:00 253 00:00:00 18 21:00:00 250 23:00:00 22 So
Hi John,
The result is:
22:00:00 253
00:00:00 18
21:00:00 250
23:00:00 22
So wrong dates are only when I use invoices.
On 29/04/2020 23:56, John Ralls wrote:
Please remember to copy the list on all replies.
I take it that that means that you do in fact use the business invoice
Please remember to copy the list on all replies.
I take it that that means that you do in fact use the business invoice
features. Let's see if that's the source of the problem. Run this query:
select t.post_date::TIME count(t.post_date::TIME) from transactions as t inner
join invoices as i on
Dimon,
I'm in Silicon Valley, so 10 hours behind you.
By "simple invoices" do you mean that some of the transactions are created
using business invoices?
My book goes back to 1993 and entry_dates begin in 2001. We changed the
transaction time from local midnight in early 2011 so `select
Hi John!
You are here finally!
Waiting for you all the day :)
All my data I have entered inside Gnucash 3.7,Ubuntu. No imports! Scheduled
are ok!
Just simple invoices inside the program!
The SQL type conversions inside Postgres give better results with 22:00 but
21:00
> On Apr 29, 2020, at 2:18 AM, finf...@gmail.com wrote:
>
> Dear John,
>
> Thank you for your response.
>
>
> I have collected some statistics from my DB.
>
> My DB has 1724 records - transactions.
>
> This is my SQL query, it is pretty simple and shows all the combinations of
> times
Dear John,
Thank you for your response.
I have collected some statistics from my DB.
My DB has 1724 records - transactions.
This is my SQL query, it is pretty simple and shows all the combinations
of times in posted_date timestamps in transactions table, number of
repetitions for that
> On Apr 28, 2020, at 3:43 PM, finf...@gmail.com wrote:
>
> Hi,
>
> I have found a strange thing trying to create my report with SQL queries from
> Postgresql Gnucash database.
>
> The column post_date in transactions table stores values in timestamp format
> (-MM-DD hh:mm:ss).
>
> If
Hi,
I have found a strange thing trying to create my report with SQL queries
from Postgresql Gnucash database.
The column post_date in transactions table stores values in timestamp
format (-MM-DD hh:mm:ss).
If I compare the post date in the transaction inside Gnucash program and
in
33 matches
Mail list logo
