Date in MySQL Laravel ORM

Question

I store my dates in timestamp type (and I see in PHPMyAdmin for simple 2015-04-14 00:00:00). Is it stupid? Better way is set to int type and save UNIX time?

How can I do query

select * from `values` where `time` < $mytimeinunix

because Model::all()->where('time','<',$mytimeinunix) is not working.

Regards

First question: Why do you want to store them in a TIMESTAMP type? That's an ancient relic from MySQL 1.0. Why not DATETIME? — tadman
– tadman, Commented Apr 14, 2015 at 20:00
I'm not good in bases. If DATETIME is better I will use it :) — ventaquil
– ventaquil, Commented Apr 14, 2015 at 20:03
@tadman What? TIMESTAMP fields are not an ancient relic. They come in very handy when dealing with timezones. — ceejayoz
– ceejayoz, Commented Apr 14, 2015 at 20:14
From the documentation: "MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval." That's a convenience method only, and time-zone handling should be done in the application anyway where it can be client specific. Plus, they're bounded, they only work up to 2038, making them a ticking time bomb. Why would you voluntarily use them? Generally it's best to use the standard DATETIME or DATE unless you have a very specific reason. — tadman
– tadman, Commented Apr 14, 2015 at 20:25
@tadman They're fine for a variety of use cases. Laravel uses them for created_at/updated_at/deleted_at columns, for example. — ceejayoz
– ceejayoz, Commented Apr 15, 2015 at 13:51

Limon Monte · Accepted Answer · 2015-04-14 20:03:57Z

3

You're looking for UNIX_TIMESTAMP:

Model::where(DB::raw('UNIX_TIMESTAMP(time)'), '<', $mytimeinunix)->get();

edited Apr 14, 2015 at 20:03

answered Apr 14, 2015 at 20:00

Limon Monte

54.8k49 gold badges191 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

tadman Over a year ago

This won't perform very well as it can't be indexed.

ventaquil Over a year ago

I can't do it with in-build ORM? Sorry for my stupid question but I learn Laravel now :<

ventaquil Over a year ago

Limonte thanks it's working fine, thank You bro :) I will accept answer soon :)

Collectives™ on Stack Overflow

Date in MySQL Laravel ORM

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related