Lucid query by datetime field


#1

Hi,

How to query by created_at(datetime)

For example i want to get all records created by today. I found a bunch of examples for Database class, but i want to query by Lucid Models.

for example i want like

User.query().where(‘created_at’,new Date())

but the above dosent work, as the bindings it take as ‘2018-12-26T11:48:21.318Z’ where as in my table its “datetime” is of “2018-12-26 16:29:38” and the resultset is empty.

Thanks


#2

At the beggining of your Controller you should invoke this

const ModelName= use('App/Models/ModelName')

After that

const data = await ModelName.query()
	.select('*')
	.where('created_at', '2018-12-18 20:59:28')
	.fetch()
return data
  • As you can see I’am using a Model to make the query

  • I need to include the query() method

  • After that I’am use of a where condition to filter data

  • Finally I show it thanks to fetch() method

If you need to format a date for example as YYYY-mm-dd then yo should do it in this way

Database.raw('DATE_FORMAT(created_at, "%Y-%m-%d")as date'))

#3

Thank you so much. So the correct way to query the date with model is to provide the string literal of a date. I tried to solve that using Date object, thats my mistake.

Thanks @ShadowPaz


#4

I would do this with moment.js which is the best javascript library for dates.

So you could do it like

const data = await ModelName.query()
	.select('*')
	.where('created_at', moment())
	.fetch()
return data

Or for example from yesterday:

const data = await ModelName.query()
	.select('*')
	.where('created_at', moment().subtract(1, 'days'))
	.fetch()
return data

#5

good option but is too much only for managing dates