Bjaeg Bjaeg - 6 months ago 18
MySQL Question

Date Counting PHP

I'm building a task list. I have two tables in my DB. Template and Tasks
The templates are structured like so

id, task_name, function, text, days_to_action

1, Touch Base, call, Call to touch base, 7


The tasks table is structured like this:

id, task_name, function, text, due_date
1, Touch Base, call, Call to touch base, 02:06:2016


The due date is calculated with this formula ($set_days is grabbing days_to_action):

$dueDate = date('d:m:Y', strtotime('+'.$set_days.' days'));


As you can see I am using Australian date format. To do the right thing I have included this at the top of all of my pages:

date_default_timezone_set('Australia/Brisbane');


On my page I am trying to display tasks that are due today or overdue.

$today = date('d:m:Y');
$task_list = $mysqli->query("SELECT * FROM task WHERE id='$linkid' && time_due <= '$today' ORDER BY datetime DESC");


The bug I have found is when the days roll over to a new month (like the example above) my code thinks 02/06/2016 (dd/mm/yyyy) is a date that comes before 28/05/2016.

Can the database only use American format? Is there an option to switch to Australian? Is there a step I am missing?
B

Answer

Normally, for storing date values, DATE datatype should be use, as it would be convineant to maintain and for data retrival.

MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format.

You can easily change the format at application level in PHP, for display or other operational purpose, but you should always Push/Pull the records in MySQL DATE datatype i.e. 'YYYY-MM-DD'.

you may change the datatype by

ALTER TABLE task CHANGE due_date due_date DATE

References

https://dev.mysql.com/doc/refman/5.6/en/datetime.html

http://www.developwebsites.net/choose-optimal-mysql-data-type/

Best practice for storing the date in MySQL from PHP