May 1, 2019 at 5:55 am #6242
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 directionMay 1, 2019 at 8:47 am #6243
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 regardsMay 2, 2019 at 12:43 am #6245
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 timeMay 2, 2019 at 9:08 am #6246
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?May 3, 2019 at 4:18 am #6248
yes the timezone is correct at Denver:
wordpress is set to denver:
notice the times in DB are still in UTC:
and look at times from the wallet:
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 itMay 3, 2019 at 8:35 am #6249
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.May 3, 2019 at 6:39 pm #6257
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
$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 endDateMay 4, 2019 at 9:52 am #6258
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 regardsMay 4, 2019 at 12:39 pm #6259
You could try something like
SELECT COUNT(*) FROM wp_wallets_txs WHERE CONVERT_TZ(created_time,'+00:00','-06:00')<=etc…
in your queriesMay 4, 2019 at 9:51 pm #6260
the host will not change the DB time
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:20May 6, 2019 at 10:49 am #6266
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.
- You must be logged in to reply to this topic.