Time Query (Query Builder 8)

Time comparison

The framework has built-in commonly used time query methods, and can automatically identify the type of time field, so no matter what type is used Time fields can all use the time query usage in this chapter.

Use the whereTime method

The whereTime method provides quick query for date and time fields. The example is as follows:

// 大于某个时间
Db::name('user')
    ->whereTime('birthday', '>=', '1970-10-1')
    ->select();
// 小于某个时间
Db::name('user')
    ->whereTime('birthday', '<', '2000-10-1')
    ->select();
// 时间区间查询
Db::name('user')
    ->whereTime('birthday', 'between', ['1970-10-1', '2000-10-1'])
    ->select();
// 不在某个时间区间
Db::name('user')
    ->whereTime('birthday', 'not between', ['1970-10-1', '2000-10-1'])
    ->select();

You can also use the following time expression for time query

// 查询两个小时内的博客
Db::name('blog')
	->whereTime('create_time','-2 hours')
    ->select();

Query a certain time interval

For time interval query, the system also provides whereBetweenTime/whereNotBetweenTime shortcut methods.

// 查询2017年上半年注册的用户
Db::name('user')
    ->whereBetweenTime('create_time', '2017-01-01', '2017-06-30')
    ->select();
    
// 查询不是2017年上半年注册的用户
Db::name('user')
    ->whereNotBetweenTime('create_time', '2017-01-01', '2017-06-30')
    ->select();

Query a certain year

Query the users registered this year

Db::name('user')
    ->whereYear('create_time')
    ->select();

Query the users registered last year

Db::name('user')
    ->whereYear('create_time', 'last year')
    ->select();

Query a certain year The data is used

// 查询2018年注册的用户
Db::name('user')
    ->whereYear('create_time', '2018')
    ->select();

Query a certain month

Query the registered users this month

Db::name('user')
    ->whereMonth('create_time')
    ->select();

Query the registered users last month

Db::name('user')
    ->whereMonth('create_time','last month')
    ->select();

Query users registered in June 2018

Db::name('user')
    ->whereMonth('create_time', '2018-06')
    ->select();

Query a certain week

Query this week’s data

Db::name('user')
    ->whereWeek('create_time')
    ->select();

Query last week Data

Db::name('user')
    ->whereWeek('create_time', 'last week')
    ->select();

Query the data for a week starting from a specified day

// 查询2019-1-1到2019-1-7的注册用户
Db::name('user')
    ->whereWeek('create_time', '2019-1-1')
    ->select();

Query a certain day

Query the users registered on that day

Db::name('user')
    ->whereDay('create_time')
    ->select();

Query the users registered yesterday

Db::name('user')
    ->whereDay('create_time', 'yesterday')
    ->select();

Query the data of a certain day using

// 查询2018年6月1日注册的用户
Db::name('user')
    ->whereDay('create_time', '2018-06-01')
    ->select();

Time field interval comparison

can support Interval comparison of two time fields

// 查询有效期内的活动
Db::name('event')
	->whereBetweenTimeField('start_time', 'end_time')
    ->select();

The above query is equivalent to

// 查询有效期内的活动
Db::name('event')
	->whereTime('start_time', '<=', time())
    ->whereTime('end_time', '>=', time())
    ->select();

Customized time query rules

You can add customization by setting time_query_rule in the database configuration file Time query rules

'time_query_rule'    =>    [
    'hour'    =>    ['1 hour ago', 'now'],
]

Time Query (Query Builder 8)

php.cn