How do I extract date & time from timestart ?

How do I extract date & time from timestart ?

autor Stephen Digby -
Počet odpovědí: 7
Connecting MS Access to moodle SQL mdl_events table gives me timestart data like:
1147321800
I presumed that it was based on seconds since 1/1/1970 and tried the access expression:

DateValue("1/1/1970")+[timestart]/60/60/24+10/24

It gives me a date that is USUALLY correct bt the hours are wrong !

Without a moodle list output built into the calendar I am getting roasted by my admin.

Can anyone help me extract real times from moodle times ?


Průměr hodnocení: -
V odpovědi na Stephen Digby

Re: How do I extract date & time from timestart ?

autor Anthony Borrow -
Obrázek: Core developers Obrázek: Plugin developers Obrázek: Testers

I would try using mysql's from_unixtime function.

SELECT FROM_UNIXTIME(875996580);
        -> '1997-10-04 22:23:00'
Průměr hodnocení: Useful (1)
V odpovědi na Anthony Borrow

Re: How do I extract date & time from timestart ?

autor Stephen Digby -
How do I use that ?
Do I have to write a "front end" program in PHP with that as part of the code ?

Průměr hodnocení: -
V odpovědi na Stephen Digby

Re: How do I extract date & time from timestart ?

autor Anthony Borrow -
Obrázek: Core developers Obrázek: Plugin developers Obrázek: Testers
I was presuming that you could use phpMyAdmin or some tool that will allow you to run sql commands. I do not know if Access will allow you to query the table but if you can and your backend is MySQL you should be able to get the results in a more meaningful format.
Průměr hodnocení: -
V odpovědi na Stephen Digby

Re: How do I extract date & time from timestart ?

autor josephine kinsley -

The time is a Unix stamp and can be taken into excel and converted without too much hassle.

For example:

If you put a value into A1 then in B1 put the formula  /86400+25569

Then convert the format of B1 to date and time 24hr you will get the date and time.

I have used this successfully to extract usage times for my manager to show when learners are accessing the VLE with the objective of providing support at busy times.

Hope this helps

Průměr hodnocení: -
V odpovědi na Stephen Digby

Re: How do I extract date & time from timestart ?

autor Stephen Digby -
Thanks for your help everyone:
Have got a system that extracts and formats dates as a list from any course I like now (as long as I connect through MS Access)

Install MySQL OBDC Driver 3.51 / Start settings control panel / admin tools/ Data Sources/ ODBC /  add new service/ select MySQL ODBC driver/ follow prompts for User & password/ open access/ get external data/ point to MySQL ODBC connection

Have put more details in Moodle Docs:
http://docs.moodle.org/en/Calendar_%28teacher%29
Průměr hodnocení: -
V odpovědi na Stephen Digby

Re: How do I extract date & time from timestart ?

autor Stuart Henry -

Hi, Im a stuydent from the UK, I think im trying to do a simular thing to you. I need to create a simplete database that will extract information from Moodle, Information that needs to be stored is,

  • Username
  • Module
  • Login Time
  • Logout Time

Im currently manually inputting information into my Access database, but was wondering if there was any simple ways of extracting the information from Moodle into Access automatically.

I have the ability to extect specific information from within Accessm but again, this information has been input manually and needs to be done automatically.

Im not the biggest expert when it comes to Mysql or PHP, is there easier solutions to using these or are these required.

Any explanation would be much appreciated. 

Stuart Henry

Průměr hodnocení: -