Open extra topbar

Timezones of Moves

If you are new here, please see some information on how to ask for support. Thank you!

Timezones of Moves

dashed-slug.net Forums General discussion Timezones of Moves

Viewing 11 posts - 1 through 11 (of 11 total)
  • Author
    Posts
  • #6242
    dynamitemedia
    Participant

    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

    #6243
    alexg
    Keymaster

    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

    #6245
    dynamitemedia
    Participant

    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

    #6246
    alexg
    Keymaster

    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?

    #6248
    dynamitemedia
    Participant

    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

    #6249
    alexg
    Keymaster

    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.

    #6257
    dynamitemedia
    Participant

    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

    #6258
    alexg
    Keymaster

    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

    #6259
    alexg
    Keymaster

    You could try something like

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

    in your queries

    #6260
    dynamitemedia
    Participant

    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

    #6266
    alexg
    Keymaster

    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

Viewing 11 posts - 1 through 11 (of 11 total)
  • You must be logged in to reply to this topic.