0

I have an old app that let's users insert dates so everyone knows when they will be on vacation. Up until now, they had text field where they would enter text as they like ("1.1,5.1,21.1-25.1") or whatever they want as it is simple text field. This kind of input excludes any chance of filtering or search. I started playing with Yii not too long ago and this is first time i need to work with multiple dates and or date ranges.

What i need is advice on how to store those dates / date ranges into database? I know Yii has it's way to store single date (i have done it before), but i have no idea if it can work with date ranges and or multiple dates.

If any of you out there had similar problem i would apriciate your advice on how to store those dates and maybe extensions you used etc.

Of course i would like to make it user friendly with date pickers and search capabilities, but i'm taking it step by step. Once i have it stored correctly, searching and filtering wont be huge pain.

Ljudotina
  • 269
  • 1
  • 3
  • 14
  • well i think it depends how you use the date range.I mean how do you want to search the dates because you are using date range and what is your search criteria? – Rafay Zia Mir Mar 17 '14 at 11:23
  • Well, i would like to be able to insert (select in datepicker) date and get list of ppl that are on vacation on that day. For now i have every user in one row. Each user has column "vacation" where they have text they enter. First thing that comes to my mind is creating seperate table with user id and date where each row would represent 1 day. Only thing is that i dont can Yii store dates that way or would i have to create custom functions to do that. I'd like to do it as "Yii" as possible, if you know what i mean. – Ljudotina Mar 17 '14 at 16:09
  • sorry mate i could not comment on time. For your problem, If you want to regularly search the dates then i think you need to make two distinct table columns like startingDate and endDate. I think this is not concerned with Yii, this has to be related with Db. You can search between ranges easily if you have distinct columns for example select * where endDate>mydate AND starDate – Rafay Zia Mir Mar 18 '14 at 18:11
  • So no native Yii solution. Not a problem. I just didint want to spend my time creating custom functions if there is something native for this (it is not my main project so i can do it whenever i have time). If you are willing you can write it as answer so i can confirm it. – Ljudotina Mar 19 '14 at 19:11

1 Answers1

0
What i need is advice on how to store those dates / date ranges into database?

Well that depends on how do you want to use the date range. It depends on what is the criteria for searching. Because If you dont need to search the dates regularly then there may be some dirty ways to accomplish this task.
But if you need to search it frequently then you should make explicit columns for starting and end dates in the database table.By making explicit tables you can search in date ranges easily. for example you can run an sql query like

SELECT * FROM yourtable WHERE startDate<="some date" AND endDate>="some date"

NOTE: You have to be careful about the format of date in your php code and format of date in database.
If you need to use date range just for calculation purposes then you can use simple php code to accomplish that.

$startDate = '2014-02-20';

$endDate = '2014-03-20';

$inputDate = '2014-02-28';
$start = strtotime($startDate);
  $end = strtotime($endDate);
  $input = strtotime($inputDate);
bool $isBetween=(($user >= $start) && ($user <= $end));

Yii way: Actually there is not yii way to work with date range through one window. Actually each framework provide basic independent access to all attributes.That does not mean you cant change the behavior. Yes you can, but you need to code more.
There are some extensions which you may find helpful in future
Adding a date range search for CGridView the easy way
How to filter CGridView with From Date and To Date datepicker

Rafay Zia Mir
  • 2,116
  • 6
  • 23
  • 49