SQL Date and Time Help

SQL Date and Time Help

by Juan F -
Number of replies: 3

Folks, I'm working quite hard in Moodle 2.8 trying to write some code in the dashboard .  I have a piece of code that returns the date and time which a user added the attendance module session and when the user took attendance using the module.

The date and time that is returned is incorrect.  The server is set to UTC (+5 hours) and I'm in EST.  Is there a piece of code that can change this so I don't have to always use an Excel formula to fix this?

I was looking into something such as  CONVERT_TZ but I'm way out of my league here.

Sample code of mine is below:

from_unixtime(atsess.sessdate, '%M %D, %Y %H %i' ) as "Session Date",  
from_unixtime(atsess.lasttaken, '%M %D, %Y %H %i' ) as "Last Taken Date"

Thanks for any help you can provide!

Average of ratings: -
In reply to Juan F

Re: SQL Date and Time Help

by David Mudrák -
Picture of Core developers Picture of Documentation writers Picture of Moodle HQ Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers Picture of Plugins guardians Picture of Testers Picture of Translators

Let me suggest not to use MySQL specific functions such as FROM_UNIXTIME() in your SQL queries (if nothing else, your code is not cross-db compatible). The common pattern we use in Moodle is to store and obtain timestamps as plain integers and then use Time API to convert them to the actual value displayed to the particular user.

Average of ratings: Useful (1)
In reply to David Mudrák

Re: SQL Date and Time Help

by Juan F -

David, I am very new to writing SQL and most of the times just learn to put things together after trial and error.  How would you suggest I write the above piece of code using the Moodle Dashboard?

Average of ratings: -
In reply to Juan F

Re: SQL Date and Time Help

by David Mudrák -
Picture of Core developers Picture of Documentation writers Picture of Moodle HQ Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers Picture of Plugins guardians Picture of Testers Picture of Translators
My point was not to try to do it purely in SQL. Fetch the timestamp from the DB as a raw integer into a PHP script and use standard Moodle API to display the result to the user.
Average of ratings: -