How to fetch already existing records in MySql with start_time and end_time in yii2?

Question

I am having tbl_batch which has

id
title
start_date
start_time
end_time
venue_id
created_by

In the database start_time and end_time is being saved as time()

Row:-

id 1
title test
start_time 10:10
end_time 11:30
venue_id 1
start_date 2020-03-01 (Y-m-d)
created_by 1

So I would like to check if time is between start_time and end_time with the given start_date .

Query I have tried :-

$model = Batch::find()->where([

'start_date' => '2020-03-01',
'venue_id' => 1

])->andWhere([

'AND',
[ '>=',
  'start_time',
  '10:20'
],
[ '<=',
  'end_time',
  '19:20'
]

]);

if($model->exists()){
      echo 'Already exists in DB';
}else
{
      echo 'Does not Exist';
}

This is not returning the correct output .

What would be the correct output? Do you expect your sample row (with id 1) to be selected? — El_Vanja, Mar 13 '20 at 10:11
could you explain exact dataType of start_time and end_time in your database — Shringiraj Dewangan, Mar 13 '20 at 11:35

score 0 · Accepted Answer · answered Mar 17 '20 at 04:09

I finally figured out :)


   $time_between = Batch::find()->where([
                   'start_date' => '2020-03-01',
                   'venue_id' => 1
                ])->andWhere([

                    'OR',
                    [
                        'OR',

                        [
                            'between',
                            'end_time',
                            $model->start_time,
                            $model->end_time
                        ],
                        [
                            'between',
                            'start_time',
                            $model->start_time,
                            $model->end_time
                        ]
                    ],
                    [

                        'AND',
                        [
                            '<=',
                            'start_time',
                            $model->start_time
                        ],
                        [
                            '>=',
                            'end_time',
                            $model->start_time
                        ]
                    ]
                ]);

How to fetch already existing records in MySql with start_time and end_time in yii2?

1 Answers1