- This topic has 10 replies, 2 voices, and was last updated 5 years, 7 months ago by
.
-
Posts
-
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
Hello,
The
created_timefield in deposit transactions coming from RPC API wallets comes from the wallet itself. It is displayed using the default WordPress timezone.Instead, the
created_timefor 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
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
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?
yes the timezone is correct at Denver:
wordpress is set to denver:
https://prnt.sc/njrr4rnotice the times in DB are still in UTC:
https://prnt.sc/njrr23and look at times from the wallet:
https://prnt.sc/njrrntthe 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
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.
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
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
You could try something like
SELECT COUNT(*) FROM wp_wallets_txs WHERE CONVERT_TZ(created_time,'+00:00','-06:00')<=etc…in your queries
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
Hello,
If your database version is earlier than 5.6, then
CONVERT_TZ()will not work and you can useDATE_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
- You must be logged in to reply to this topic.