column = claculation between two date fields.

column = claculation between two date fields.

by John Samuel -
Number of replies: 5

ok we have  2 columns:

School Start Date l School End Date   how do i create a 3rd column that calculates the date difference between the two fields  in days ?

Thanks,

Moodlers let's work on this as it will help alot of us !!

Average of ratings: -
In reply to John Samuel

Re: column = DATEDIFF between two date fields.

by John Samuel -

How do i write an sql qury to say datdiff of column A and Column B ?

Thanks

Average of ratings: -
In reply to John Samuel

Re: column = DATEDIFF between two date fields.

by Chris Mayo -

I'm not a SQL expert by any means, so I can't tell you exactly, but I found this link the other day when I was poking around for some stuff on a project I am working on.

http://www.w3schools.com/sql/func_datediff.asp

In theory this will work:

SELECT DATEDIFF(day,'2008-08-05','2008-06-05') AS DiffDate

What I don't know (and don't have a setup to test) is if it can be written tied to fields to calculate between columns like you were asking.

This, like a couple of functions in the interaction report, are things that I'd like to know how to do. Calculating the time spent, and I need to figure out how to get the last accessed date.

Average of ratings: -
In reply to Chris Mayo

Re: column = DATEDIFF between two date fields.

by John Samuel -

Thank you Chris!

Much appreciated, i believe i tried the same syntax but changing the dates with the field names like:

SELECT DATEDIFF(day,'startdate','enddate') AS DiffDate

or

SELECT DATEDIFF(day,startdate,enddate) AS DiffDate

All i get is NULLS even if the profile fields actually have data in it..

I hope someone with higher experience will step in and shed some light here.

J.Sam

 

Average of ratings: -
In reply to John Samuel

Re: column = DATEDIFF between two date fields.

by Marcus Wynwood -

Hi John,

You'll need to use the FROM_UNIXTIME function for dates on the Moodle DB.

Something like this worked for me:

 

datediff(FROM_UNIXTIME(timestart), FROM_UNIXTIME(timeend))

Average of ratings: Useful (1)
In reply to Marcus Wynwood

Re: column = DATEDIFF between two date fields.

by Kelly Thomas -

I'm having two issues related to this function. I'm calculating a time difference which, on average, is about 30 to 45 minutes. In one column I need it formatted HH:MM:SS and in the other I just need straight seconds.

1. In the first field I'm getting all 0's (or, more specifically, "00:00:00"). Here's my syntax:

SEC_TO_TIME(
datediff(FROM_UNIXTIME(prefix_quiz_attempts_0.timestart), FROM_UNIXTIME(prefix_quiz_attempts_0.timefinish))) AS Elapsed

2. In the second field I'm getting seconds -- but not for everyone, even though everyone has a start time and everyone has an end time. Here's the syntax for this one:

TIME_TO_SEC(FROM_UNIXTIME(prefix_quiz_attempts_0.timefinish) - FROM_UNIXTIME(prefix_quiz_attempts_0.timestart)) AS Elapsed_Seconds

Any help?

Average of ratings: Useful (1)