Change date format for user report (bulk user actions)

Change date format for user report (bulk user actions)

by Emanuele Peri -
Number of replies: 10

Hello everyone,

is there any way to have date fields in "normal" format (day/month/year or Year/date/month) and not in Unix time? I would like to have the report in excel with the right format, without having to use a formula on excel to do the conversion.

I need it for a birthday custom field.

Thank you

Average of ratings: -
In reply to Emanuele Peri

Re: Change date format for user report (bulk user actions)

by Rick Jerz -
Picture of Particularly helpful Moodlers Picture of Testers
What is more "normal" than Moodle getting a date from Linux (UNIX), like 1447384951. It is only people who are not normal! 🤔

As far as I know, in Moodle's database, the Linux time is used.  "Normal" people will sometimes see these dates like calendar dates in some of Moodle's reports, but that is because a programmer knew what format they wanted to appear in their "normal" world.  In SQL, you can do whatever you want.

If you are not good with SQL, or using a report from Moodle, Excel can do anything, too.  Where you find it easier to apply a formula is really up to you.

In Excel, you can create your own User-Defined functions.  This is what I would do if I was finding myself recreating a formula too often.

I decided to take this non-normal approach in replying to you.  I do not mean to be offensive.
Average of ratings: Useful (1)
In reply to Rick Jerz

Ri: Re: Change date format for user report (bulk user actions)

by Emanuele Peri -
Hi Rick,

thank you for your reply, non problem for the approach, don't worry about your approach, maybe I should have used another term, as "readable for most" :D .

I have already found a solution for apply the formula on excel, but I prefer to apply it on SQL (I'm not good with SQL, but I can try). The problem is that I don't know which php file the function is in to obtain the data reported on the excel file (using the download in bulk user actions functionality).

Do you know what it is?
Or if you knew some other method to achieve my purpose (an excel file with profile info and custom field date in day / month / year format) it would be perfect.
Average of ratings: -
In reply to Emanuele Peri

Re: Ri: Re: Change date format for user report (bulk user actions)

by Rick Jerz -
Picture of Particularly helpful Moodlers Picture of Testers

I am not sure about which exact report you desire.  However, you might need to begin by seeing if you can access your database, and execute a SQL query, such as:

Select * from mdl_user

This will answer a lot.

One could probably do this with php, but try it from either the mysql command, or from a utility, such as phpMyAdmin (if you have it.)

Also, explore: List of SQL Contributed reports

My guess is that for a while, you will be faster with Excel.

Average of ratings: Useful (1)
In reply to Emanuele Peri

Re: Ri: Re: Change date format for user report (bulk user actions)

by C Behan -
Hi Emanuele,
 I use this in configurable reports DATE_FORMAT(FROM_UNIXTIME(COLUMNNAME),'%d-%m-%Y')
 e.g. I could have


SELECT subject, message, DATE_FORMAT(FROM_UNIXTIME(created),'%d-%m-%Y') 

FROM prefix_forum_posts


 If you want to include times or other date formats etc see https://www.w3schools.com/sql/func_mysql_str_to_date.asp for the list.

 Catherine
Average of ratings: -
In reply to Emanuele Peri

Re: Change date format for user report (bulk user actions)

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
where are you seeing dates as a unix timestamp.

If you're using SQL directly then it's probably from_unixtime(fieldname) that you want
Average of ratings: Useful (1)
In reply to Howard Miller

Re: Change date format for user report (bulk user actions)

by Rick Jerz -
Picture of Particularly helpful Moodlers Picture of Testers
Yep, and here is the Excel function:

=(A1/86400) + + DATE(1970,1,1)
Average of ratings: Useful (1)
In reply to Rick Jerz

Re: Change date format for user report (bulk user actions)

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
I like mine better big grin big grin
Average of ratings: -
In reply to Howard Miller

Re: Change date format for user report (bulk user actions)

by Rick Jerz -
Picture of Particularly helpful Moodlers Picture of Testers
Yeah, but you didn't finish! How about:

from_unixtime(ls.timecreated, "%W, %M %d, %Y, %h:%i:%s %p") AS TimeCreateDate

Now Excel looks cleaner!!!

Okay, I hope the OP understands what we are playing around with. As you know, dates can sometimes be the most challenging datatypes to convert into other systems.

C Behan  is right on target and illustrates that there are all kinds of ways to format dates.  (Thanks, C.)
Average of ratings: Useful (1)
In reply to Rick Jerz

Re: Change date format for user report (bulk user actions)

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
Don't forget whatever your local version of daylight saving time is either wink
Average of ratings: -
In reply to Emanuele Peri

Re: Change date format for user report (bulk user actions)

by Ivan Cedano - 

Hello, I have the same situation, how did you solve it? From PHP programming or any available plug-in. Thanks and regards

Average of ratings: -