Timezones of Moves

[Anonymous]

we are having a 6 hour difference on moves…

where can we go in and set our timezone? which file?

im using my old modified litecoin adapter if that helps send me in right direction

[alexg]

Hello,

The created_time field in deposit transactions coming from RPC API wallets comes from the wallet itself. It is displayed using the default WordPress timezone.

Instead, the created_time for internal transfers (moves) has to come from WordPress.

First set the UTC time correctly on your WordPress server. Then, visit the WordPress admin in Settings -> General -> Timezone and set the timezone correctly. Check that the dates that WordPress reports make sense in your location. Then, check the Tranactions list in admin and make sure that the created time for new transactions is displayed correctly.

The transaction times, when displayed in the frontend are rendered with the timezone of the user’s browser, so you can’t control that.

with regards

[Anonymous]

changing the UTC time on the wordpress makes no changes to the internal moves

i have changed it to several different times and it is the same each time

[alexg]

OK. Did you check the server time or only the timezone setting?

The plugin stores the GMT time, then uses the current timezone to offset the time before displaying it.

You would only see a change in new transactions, not in the old ones. Did you attempt a new “move” transfer?

[Anonymous]

yes the timezone is correct at Denver:

https://prnt.sc/njrqpz

wordpress is set to denver:
https://prnt.sc/njrr4r

notice the times in DB are still in UTC:
https://prnt.sc/njrr23

and look at times from the wallet:
https://prnt.sc/njrrnt

the wallet is hosted on a VPS in EST which is 2 hours difference

But that does NOT explain for the 6 hour difference we have going into the Database

i have all records printing to a CSV so its causing havoc with book keeping

any idea if i can offset it 6 hours to get it right? i know i would have to prob change it each time you have a update but it may be worth it

[alexg]

OK thank you for the screenshots. I’ll summarize some observations so we can be on the same page:

– The times in the database are meant to be stored in UTC.

– Your server’s timezone is set correctly to GMT-6.

– Your wallet server is in GMT-4, but this should not matter, since the RPC API of bitcoin-like wallets returns timestamps in GMT.

– Assuming the DB view you show me is sorted by primary key, it seems that all the times are stored in correct UTC.

– In the frontend, you see times for withdrawals offset by 4 hours. (This probably has to do with the timezone of the machine where the browser runs.)

Because I don’t know when you performed these transactions, it’s still unclear to me:

1. For internal move transfers, is the correct UTC timestamp shown in the database? If not, what’s the offset to the UTC time when you performed the tranasaction?

2. For deposits/withdrawals, is the correct UTC timestamp shown in the database? If not, what’s the offset to the UTC time when you performed the tranasaction?

Once I understand these two things I can determine what’s the right way to offset the times in the frontend and in your CSV export. I am still not clear on whether all the UTC timestamps are correct in the database, so please try to answer 1 and 2.

Thank you.

[Anonymous]

they both are going in at the same time which is the + 6 hours

everything done by the wallet is +6 hours ahead, withdrawals, deposits and moved all are + 6

this causes the mysql query’s that we do are all off and displayed on next day

Example:
$sql3 = "SELECT COUNT(*) FROM $Table WHERE DATE(time)>='$fromDate' AND DATE(time)<='$endDate' AND location='$location' AND account='$account' ";

fromDate would be formatted like this 2019-5-02 same for endDate

[alexg]

Hello,

I see two problems here:

1. The times in your database are not “+6” hours, they are in UTC and your timezone is “-6” hours.

If all the database entries are in correct UTC time, rather than local time, then you should leave them as they are (i.e. not modify the original data).

Then, it is easy to construct a query that converts this data to any timezone and choose and export a particular day according to your local time.

2. This is not how you would construct such an SQL query. You’d have to use MySQL’s date/time functions. I would start by creating a temporary table or view or subquery with the offset data ( see CONVERT_TZ() ), then select from that data.

with regards

[alexg]

You could try something like

SELECT COUNT(*) FROM wp_wallets_txs WHERE CONVERT_TZ(created_time,'+00:00','-06:00')<= etc…

in your queries

[Anonymous]

the host will not change the DB time

CONVERT_TZ

will not work for same reason the host will not change they told me to use datesub

i have tried this:

$sql3 = "SELECT COUNT(*) FROM $walletTable WHERE DATE_SUB(created_time, INTERVAL 6 HOUR);)>='$fromDate' AND DATE_SUB(created_time, INTERVAL 6 HOUR);)<='$endDate' AND account='$account' AND category='move' ";

and the variables $fromDate and $endDate look like this 2019-05-03 22:20:20

[alexg]

Hello,

If your database version is earlier than 5.6, then CONVERT_TZ() will not work and you can use DATE_SUB() or some other method. I believe you should remove some extra characters from your query, so rewrite your code like so:

$sql3 = "SELECT COUNT(*) FROM $walletTable WHERE DATE_SUB(created_time, INTERVAL 6 HOUR) >='$fromDate' AND DATE_SUB(created_time, INTERVAL 6 HOUR)<='$endDate' AND account='$account' AND category='move' ";

If you don’t trust your inputs, you should use $wpdb->prepare() instead of relying on PHP string interpolation to insert the variable values. But if you are in control the inputs it should not matter.

with regards

[Author]

Posts