/    Sign up×
Community /Pin to ProfileBookmark

Unable to convert UTC to PST when website is in 000webhost

Hello!

We are working on a project and one of its features is to store the login and logout times of a user when he/she access the website. It worked fine in localhost but when we transferred it on the the hosting site the login times were converted into UTC is there a way to convert it back to Philippine Standard Time?

Thank You!

What we did:
The Login time script:

“`
<?php

error_reporting(E_ALL);
ini_set(‘display_errors’, 1);

include(‘../server.php’);

$id = $_SESSION[‘user’][‘id’];
//var_dump($_SESSION);
$dataSend = $_POST[‘dataSend’];

date_default_timezone_set(‘Asia/Manila’);
//$date = date(“Y-m-d”);
$date=date(“n-j-Y”);

date_default_timezone_set(‘Asia/Manila’);
$time = date(“h:i:A”);

//create connection
$status = ‘1’;
//check connection

//start of the new script
$sql = “INSERT INTO attendancetracking (id, time_in, Room, status)
VALUES ($id, NOW(), ‘$dataSend’, $status)”;
//end of the new script

if ($db->query($sql) === TRUE) {
echo “new attendance was created”;

} else {
echo “2: ” . $sql . “<br>” . $db->error;
//echo”2″;
}

$db->close();

?>
“`

We also tried another version using “`SET time_zone“` but still no luck
this:

“`
<?php

include(‘../server.php’);

$id = $_SESSION[‘user’][‘id’];
//var_dump($_SESSION);
$dataSend = $_POST[‘dataSend’];

$sql = “SET time_zone = ‘+08:00′”; ***we also tried replacing +08:00 with Asia/Manila but still no luck***
$result = $db->query($sql);
//var_dump($db->error);

//create connection
$status = ‘1’;
//check connection

//start of the new script
$sql = “INSERT INTO attendancetracking (id, time_in, Room, status)
VALUES ($id, NOW(), ‘$dataSend’, $status)”;
//end of the new script

if ($db->query($sql) === TRUE) {
echo “new attendace was created”;

} else {
echo “2: ” . $sql . “<br>” . $db->error;
//echo”2″;
}

$result = $db->query($sql);

$db->close();

?>
“`

to post a comment
PHP

9 Comments(s)

Copy linkTweet thisAlerts:
@NogDogDec 01.2020 — What column type is time_in defined as in your database table, and what DBMS are you using (e.g. MySQL, PostgreSQL, etc.)?
Copy linkTweet thisAlerts:
@NogDogDec 01.2020 — PS: As long as you're using NOW() in your query (which is perfectly reasonable), the PHP date_default_timezone_set() won't affect it. If the column type is timestamp, I think it will still be stored as UTC, but if you do that SET timezone... thing before doing any queries against the data, then it should read it in the specified timezone (I think?).

Above based on skimming through https://dev.mysql.com/doc/refman/8.0/en/datetime.html
Copy linkTweet thisAlerts:
@frncsknauthorDec 01.2020 — @NogDog#1625524

Hello! this is my time in and out. and I'm using mysql
``<i>
</i>time_in
datetime DEFAULT NULL,
time_out datetime DEFAULT NULL,
```
Copy linkTweet thisAlerts:
@NogDogDec 02.2020 — Played around with things a bit in sqlfiddle.com. The short answer: I think it's a bit more effective to use timestamp instead of datetime. Then you can use set @@time_zone = '+08:00'; both when inserting and later when reading -- and you can even use a different timezone for the read if that makes sense (e.g. for user localization). Not sure what the @@ does, but I saw that in the manual, and it seemed to work.

Hope this link works (not sure if it has a limited lifetime?): http://sqlfiddle.com/#!9/9003a9/5/5
Copy linkTweet thisAlerts:
@frncsknauthorDec 02.2020 — @NogDog#1625534 Thank you so much looking into this right now
Copy linkTweet thisAlerts:
@frncsknauthorDec 02.2020 — @NogDog#1625534 I tested your suggestion. it works but sometimes if I refresh the page it reverts back to UTC then if I refresh again it will return to Philippine Standard Time

This is the PST
``<i>
</i>Username ID Time In Time Out Date User Type Room
Teacher 3 11:47AM 11:47AM 12-2-2020 user
Teacher 3 11:47AM 11:47AM 12-2-2020 user
Patima 2 11:35AM 12-2-2020 user
Patima 2 11:36AM 12-2-2020 user
Patima 2 11:36AM 12-2-2020 user
Patima 2 11:42AM 11:42AM 12-2-2020 user
Patima 2 11:42AM 11:42AM 12-2-2020 user<i>
</i>
`</CODE>
If I refresh the page sometimes it reverts back to UTC
<CODE>
`<i>
</i>Username ID Time In Time Out Date User Type Room
Patima 2 03:35AM 12-2-2020 user
Patima 2 03:36AM 12-2-2020 user
Patima 2 03:36AM 12-2-2020 user
Patima 2 03:42AM 03:42AM 12-2-2020 user
Patima 2 03:42AM 03:42AM 12-2-2020 user
Teacher 3 03:47AM 03:47AM 12-2-2020 user
Teacher 3 03:47AM 03:47AM 12-2-2020 user <i>
</i>
`</CODE>
The code I used:
<CODE>
`<i>
</i>&lt;?php

error_reporting(E_ALL);
ini_set('display_errors', 1);


include('../server.php');

$id = $_SESSION['user']['id'];
//var_dump($_SESSION);
$dataSend = $_POST['dataSend'];

//create connection
$status = '1';
//check connection

$sql = "SET @@time_zone = '+08:00'";
$result = $db-&gt;query($sql);

if($db-&gt;connect_error) {

die("connection failed: " . $db-&gt;connect_error);

}

//start of the new script
$sql = "INSERT INTO attendancetracking (id, time_in, Room, status)
VALUES ($id, NOW(), '$dataSend', $status)";
//end of the new script

if ($db-&gt;query($sql) === TRUE) {
echo "new attendace was created";

} else {
echo "2: " . $sql . "&lt;br&gt;" . $db-&gt;error;
//echo"2";
}

$result = $db-&gt;query($sql);

$db-&gt;close();<i>
</i>
`</CODE>
and when testing the code it throws me this message but the <C>
`time_in`</C> still gets logged into the db:
<CODE>
`<i>
</i>Notice: Undefined index: dataSend in /storage/ssd4/872/15166872/public_html/server/backend/GetDate.php on line 11
new attendace was created <i>
</i>
`</CODE>
this is the origin of the message <C>
`$dataSend = $_POST['dataSend'];``
Copy linkTweet thisAlerts:
@NogDogDec 02.2020 — > @frncskn#1625538 this is the origin of the message $dataSend = $_POST['dataSend'];

That would seem to be an unrelated issue -- still worth debugging/fixing, but nothing to do with time settings? If you're refreshing a page that depends on POST data, that may be problematic, possibly (as opposed to resubmitting a form).

As far as the timezone stuff not always working, maybe verify that the query that sets the timezone actually succeeded? (Or, I like to just set the PDO error mode to exceptions and then catch anything that fails via a try/catch block.)
Copy linkTweet thisAlerts:
@frncsknauthorDec 02.2020 — > @NogDog#1625540 That would seem to be an unrelated issue -- still worth debugging/fixing, but nothing to do with time settings? If you're refreshing a page that depends on POST data, that may be problematic, possibly (as opposed to resubmitting a form).

I figured this out. This was supposed to be information regarding what Room is the user when he/she logs in or out of the system. but since its not available yet that's why this error is showing my bad.

> @NogDog#1625540 As far as the timezone stuff not always working, maybe verify that the query that sets the timezone actually succeeded? (Or, I like to just set the PDO error mode to exceptions and then catch anything that fails via a try/catch block.)

Can you give an example where to put this? I used mysqli not PDO is that possible to use both? Because there are instances when I refresh the page multiple times where it doesn't revert back to UTC not even once, but there are also instances where just one press of the refresh button then it reverts to UTC then press the refresh again it reverts back to PST.
Copy linkTweet thisAlerts:
@NogDogDec 02.2020 — Oh, sorry, I use PDO all the time and just jumped to a conclusion. I was just referring to where you do this:
<i>
</i> $sql = "SET @@time_zone = '+08:00'";
$result = $db-&gt;query($sql);

Maybe check that $result is true? (No idea why it would sometimes work and sometimes not, just kind of grasping at straws right now. :) )
×

Success!

Help @frncskn spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 BETA 5.4,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @Yussuf4331,
tipped: article
amount: 1000 SATS,

tipper: @darkwebsites540,
tipped: article
amount: 10 SATS,

tipper: @Samric24,
tipped: article
amount: 1000 SATS,
)...