Sort and Filter according to date OR time from a timestamp field in DB

Yii 2.0 General Discussions
1

I have a DB with a "timestamp" field, in the view I have a GridView where I devided the DB field "timestamp" into two different columns.

formatted as date and the other one formatted as time.

I would like to know if it’s possible to sort & filter the two different columns in my GridView using only one “timestamp” field in my DB .

is there a way to do this from yii2 or should I just create separate date and time fields in the DB ?

2

In MySQL, you can select a timestamp as a string that describes a date or a time by using "FROM_UNIXTIME()" function.

For example:




SELECT FROM_UNIXTIME(timestamp, '%Y-%m-%d') ... "2015-11-18"

SELECT FROM_UNIXTIME(timestamp, '%H:%i:%s') ... "19:07:45"

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_from-unixtime

(Any other db should have a same kind of function.)

And look at this section of the guide:

Active Record - Selecting extra fields

3

To sort GridView columns, you could create a sql view from data (with two columns) and then create the relative model.

4

Thank you for your answers.

Now that I’m thinking, since I want to search by “date” or “time” ,

using only timestamp isn’t probably the best design for performance, since my query will not be indexed.

correct me if I’m wrong but I will probably split “timestamp” into two separate fields “date” and “time”.

5

You might be right. I’m sorry, but I don’t know for sure about the performance.

BTW, why do you want to sort and/or filter the records by "time" and "date" separately? What does it mean to the end users? What use case do you have in mind?