UNIXTIME off by a few hours...how to add hours to it?

UNIXTIME off by a few hours...how to add hours to it?

by Juan F -
Number of replies: 11

Greetings everyone,

I have a report that I run which returns assignment due dates/times.  This allows me to see if my faculty have set-up the correct dates/times.  I am using the Moodle Dashboard.  I have a piece of code that converts the Unixtime.  The problem is that the time is always off by a few hours.  Is there a WAY to add (or subtract) a few hours using SQL?

Here is the snippet of code that I use which converts the UNIXTIME into Moodle date:

FROM_UNIXTIME( ma.duedate, '%m/%d/%Y %H:%i %p' ) AS "Due Date/Time",

Thank you for any assistance.

Chris

Average of ratings: -
In reply to Juan F

Re: UNIXTIME off by a few hours...how to add hours to it?

by Juan F -

If I left the time as unixtime, I would use Excel to convert it. I would type the following to convert this to the correct date/time:  =((F2+3600)/86400)+25569+(-5/24).

*F2 = the cell with the unixtime date

**+3600 is what I have to add to make the time/date correct.

Average of ratings: -
In reply to Juan F

Re: UNIXTIME off by a few hours...how to add hours to it?

by Visvanath Ratnaweera -
Picture of Particularly helpful Moodlers Picture of Translators
3600 sec is 1 hour. So the server is in the neighbouring time zone to the west. Just enter 'date' on the command line to get servers default time zone.
$ date
Mon Mar 14 09:27:49 IST 2016

The time conversion itself can be easily done in the shell:
$ date --date="@0"
Thu Jan  1 05:30:00 IST 1970

$ TZ=UTC date --date="@0"
Thu Jan  1 00:00:00 UTC 1970
Average of ratings: -
In reply to Visvanath Ratnaweera

[slightly OT] Meet the Guy Whose Software Keeps the World’s Clocks in Sync

by Visvanath Ratnaweera -
Picture of Particularly helpful Moodlers Picture of Translators
http://spectrum.ieee.org/tech-talk/computing/networks/meet-the-guy-whose-software-keeps-the-nations-clocks-in-sync

I know, it is slightly OT, but is a nice piece of popular science. Found, BTW, in https://tech.slashdot.org/story/16/03/16/185243/meet-the-guy-whose-software-keeps-the-worlds-digital-clocks-in-sync.
Average of ratings: -
In reply to Juan F

Re: UNIXTIME off by a few hours...how to add hours to it?

by lior gil -
Picture of Core developers

This might be a timezone issue. When you generate the SQL query, are you calling PHP's time(), or Moodle's usertime()?

In my experience this is the usual cause of a time difference in the DB.

Average of ratings: -
In reply to Juan F

Re: UNIXTIME off by a few hours...how to add hours to it?

by Jon Bolton -
Picture of Particularly helpful Moodlers Picture of Testers

Check the timezone setting in your Moodle admin pages - http://www.yourmoodle.com/admin/settings.php?section=locationsettings

Or change the timezone on the server that your Moodle is running on. If you're on a shared server, setting a timezone might not be allowed - but you should be able to overwrite it through your htaccess file...

#Adjust default time zone
SetEnv TZ Europe/London

See http://www.php.net//manual/en/timezones.php for your nearest timezone.

Average of ratings: Useful (1)
In reply to Juan F

Re: UNIXTIME off by a few hours...how to add hours to it?

by Darko Miletić -

What you are seeing is a timezone issue. FROM_UNIXTIME uses the MySQL timezone (whatever that may be) when doing the conversion.

In order to get the desired timezone you can do these things:

global $DB;
$sessiontz = $DB->get_field_sql('SELECT @@session.time_zone');
$dt = new DateTime('now', \core_date::get_user_timezone_object());
$tzdiff = $dt->getOffset();
$tzhours = (int)($tzdiff / HOURSECS);
$tzmins = (int)(($tzdiff % HOURSECS) / MINSECS);
$res = sprintf("%+'03d:%'02d", $tzhours, abs($tzmins));
$DB->execute("SET SESSION time_zone='{$res}'");
// Execute here your query!
$records = $DB->get_records_sql($sql, $params);
// Do stuff.
// Revert to original session timezone.
$DB->execute("SET SESSION time_zone='{$sessiontz}'");

The above code get's the mysql session timezone, stores it, determines the moodle timezone and configures current db session to use that timezone. Than you execute your query do whatever you need to do and finally set session to original value.



Average of ratings: Useful (1)
In reply to Darko Miletić

Re: UNIXTIME off by a few hours...how to add hours to it?

by Juan F -

Darko, thank you for the help.  I am using the Moodle Dashboard to write/run my SQL reports.  Is this information able to be added to the Moodle Dashboard in order to run the correct reports?  if not, is there a way to do this in the Moodle Dashboard?

Thank you.

Average of ratings: -
In reply to Juan F

Re: UNIXTIME off by a few hours...how to add hours to it?

by Darko Miletić -

No. You can not do this in dashboard. In case you need to use dashboard there is something else you can do:

Find out the timezone of your mysql server and your timezone in hours than alter your query to do this:

SELECT field1, field2, field3, CONVERT_TZ(FROM_UNIXTIME(field4), 'mysql timezone','my timezone') AS datefield FROM sometable;

Since we can not assume that mysql server will have installed named timezones it is better to use numeric values. Mysql accepts timezones in format +-HH:MM, so if your database server is in UTC timezone than you should use '+00:00' and if you are in EST (now EDT) timezone than that will look like this '-04:00' so for that case query should look like:

SELECT field1, field2, field3, CONVERT_TZ(FROM_UNIXTIME(field4), '+00:00','-04:00') AS datefield FROM sometable;

Apply that conversion to any field you need converted in the query and that's it.

I know that some people may object to this and say - "but you can use @@session.time_zone instead of hardcoding". Unfortunately that would work only in cases where MySQL server has named timezones installed which is not default on many setups so can not and should not be assumed.


Average of ratings: Useful (2)
In reply to Darko Miletić

Re: UNIXTIME off by a few hours...how to add hours to it?

by Juan F -

Darko, this is wonderful and very helpful.  When I run the code in the Moodle Dashboard, it returns the following date/time format: yyyy-mm-dd hh:mm:ss (e.g. 2016-03-21 00:00:00).  While I have no trouble adjusting this in Excel, is there a way I can get this to return in the following date/time format: mm-dd-yyyy 00:00 AM/PM (e.g. 03-21-2016 05:15:25 AM)?  I found this may be what I'm looking for: %m/%d/%Y %r but I'm unsure how to add it to the code.

Best, Chris

Average of ratings: -
In reply to Juan F

Re: UNIXTIME off by a few hours...how to add hours to it?

by Darko Miletić -

The function you need here is DATE_FORMAT which permits any custom format applied to timestamp.

SELECT DATE_FORMAT(CONVERT_TZ(FROM_UNIXTIME(1455538440), '+00:00', '-03:00'), '%m/%d/%Y %r') AS datevalue




Average of ratings: Useful (2)
In reply to Darko Miletić

Re: UNIXTIME off by a few hours...how to add hours to it?

by Juan F -

Thank you very much Darko.  You solved the problem I was having.

Best, Chris

Average of ratings: -