SQL reading of dates in the tasks table

1

Hello,

I have been using kanboard since its first release, in 2014, for:

  • Monitoring of my activity
  • The management of certain projects

I want to plan and monitor my team’s activity load.

To do this, I use tasks with start and end dates, which allows me to keep track of the schedule and load.

In order to produce a dynamic dashboard, I run an sql query to determine the load by category.

To calculate the time spent per task, I use the following sql function on the tasks table:
sec_to_time (date_due - date_started)

I am getting the time spent in hh: mm: ss format.

I would like to extract the year, month, and day from the date_due field to determine the week, but I cannot determine the format of this data.

Can you help me please ?

Thanks in advance
Philippe

2

date_due is stored as a unix time stamp.

3

Thank you very much for the speed of your response, @creecros

I can now use the required information with the corresponding sql functions:

from_unixtime(date_due,"%Y") to get the year on 4 digits

from_unixtime(date_due,"%u") to get the number of the week

from_unixtime(date_due,"%d/%m/%Y") to get the task finish date in French format

Thanks again and have a good evening.

Philippe