[amform]

Hello,
I created a before trigger that query some information from the database(dates), but some users are in Asia.
I would like to know if its possible to retrieve the date and change it to the right timezone before inserting the data to the dynaform field
I tried this code but it didn't work:

Code: Select all

$qry = 'SELECT DATE, COUNTRY FROM DATABASE WHERE user = USERNAME';

$result = executeQuery($qry);

if(is_array($result))
{
    if(count($result)>0)
    {
        foreach($result as $row){
          if($row['COUNTRY'] == "Asia"){
           $date = new DateTime($row['DATE'], new DateTimeZone('Asia/Singapore'));
           @@Date = $date;
                }
           }
     }
}

Thanks for the help
Regards

[amosbatto]

If you are using the logged-in user in ProcessMaker, then your code should be something like this:

Code: Select all

$username = @@USR_USERNAME;
$qry = "SELECT DATE, COUNTRY FROM MY_TABLE WHERE USERNAME='$username'";
$result = executeQuery($qry);
if (!is_array($result) or count($result) == 0) {
   throw new Exception("Unable to find user '$username' in MY_TABLE");
}
@@Date = $result[1]['DATE'];
if ($result[1]['COUNTRY'] == "Asia") {
    $date = new DateTime(@@Date, new DateTimeZone('Asia/Singapore'));
    @@Date = $date->format('Y-m-d H:i:s');
}

[amform]

Hi amosbatto,
Thanks for the answer.
I don't know why but I'm getting the same date that the query retrieved. As an example if I have a datetime data in the table being 2017-11-16, after the code I still get 2017-11-16 instead of 2017-11-17 as it should be in Asia.
Do you know what could be the issue?
Thanks

[amosbatto]

I tried it and see that the code I gave you in the previous post doesn't work because there is no hours:minutes in the original datetime.

If you are using the Enterprise Edition, there is automatic datetime conversion based on the user.

If you are using the Community Edition, then you can use this trigger code to convert the time zone:

Code: Select all

if (!empty(@@currentTime)) {
    //if the user didn't select the hour:minutes:seconds, then strip time and add current time:
    if (substr(@@currentTime, 10, 9) == " 00:00:00") {
       @@currentTime = substr(@@currentTime, 0, 10) . date(" H:i:s");
    }
    
    $oDT = new DateTime(@@currentTime);     
    $oDT->setTimezone(new DateTimeZone('Asia/Singapore'));
    @@asiaTime = $oDT->format('Y-m-d H:i:s');
} 

Here is a sample process with this trigger:

Convert_Time_Zone-2.pmx

(30.08 KiB) Downloaded 416 times

Note: If using the Enterprise Edition, then display the "asiaTime" variable in a text field so that ProcessMaker doesn't automatically convert it to the user's time zone.

[amform]

Thanks amosbatto that works!