Bjaeg Bjaeg - 1 year ago 109
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 Source

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