CODEWITHSUNDEEP
phpmysqldate
Bjaeg
Bjaeg

Reputation: 338

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

Upvotes: 1

Views: 63

Answers (1)

Hytool
Hytool

Reputation: 1368

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

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 with DATE datatype i.e. 'YYYY-MM-DD'.

You may change the datatype by using:

ALTER TABLE task CHANGE due_date due_date DATE

References

Upvotes: 1

Related Questions