How to filter on a date ?

[charled]

Hi everyone,

In mm_events, I got a Date(Timestamp) attribute named event_date. Each event will be on april 21, 22, 23 or 24. Trying to create a specific filtersetting for April 24 events, I've selected simple lookup and event_date.

But in 'default' selct field, I got as many times this date as I got events with this date. To be clearer : if I had 3 events with april 24, I got 3 times april, 24 in default select.

I went to see in mysql table. Dates are stored with a 10 digit integer where 8 first are equal but last 2 are different for each record. That's why simple lookup displays 3 times the april 24. But is it normal ?

I can replace my Date(Timestamp) attribute by a simple text attribute (or a select attribute to a text attribute in another mm_table) but this could be possible to do it with real dates ? No ?

Thanks for ideas or good syntax…

[zonky]

e.g. http://metamodels.readthedocs.org/de...ustom-sql.html

look at "Filterung nach Datum"

[charled]

Hi,

I did try it too with FROM_UNIXTIME() or UNIX_TIMESTAMP() but I still get a 0 rows result.

For the test, I used the same date (24-04-2016) in two records but in the mysql date field :
- first is 1461402166
- second is 1461488576

I've just tested with manually entering the date :
- first is now : 1461489344
- second is now : 1461489289

In the attribute settings, 'Date' schema is selected.

So what's wrong ?

[zonky]

all values saved as unix timestamp at database...

pls post your SQL qyuery

[charled]

I've tried many possibilities :

SELECT id FROM mm_representations WHERE UNIX_TIMESTAMP (`date_tmstp`) = '24/04/2016'
SELECT id FROM mm_representations WHERE UNIX_TIMESTAMP (`date_tmstp`) = '24-04-2016'
SELECT id FROM mm_representations WHERE UNIX_TIMESTAMP (`date_tmstp`) = '24042016 '
SELECT id FROM mm_representations WHERE UNIX_TIMESTAMP (`date_tmstp`) = '2016/04/24'
SELECT id FROM mm_representations WHERE UNIX_TIMESTAMP (`date_tmstp`) = '2016-04-24'
SELECT id FROM mm_representations WHERE UNIX_TIMESTAMP (`date_tmstp`) = '20160424'

Hey ! I've just noticed : in mysql db, for each record, the date_tmstp field stores the same integer as tstamp field. So, date_tsmtp is really acting as a timestamp and not only recording in Unix format the date entered or selected in datepicker.

Perhaps it is not the right field for what I need but I tried with numeric, decimal and text and found none I could use date formatting (or the date picker).

Edit : I'm using metamodels/bundle_all. Is there some other attributes with nightlies version ?

[zonky]

Contao (and MM) store date and date-time as unix timestamp (integer) but MySQL works normaly with the format like "YYYY-MM-DD"

if you compare, you must convert on to another...

e.g. http://www.epochconverter.com/programming/mysql

[charled]

Ok but :
- why my 2 records don't store the same integer in date_tmstmp while I entered the same date with the datepicker ?
- and why, on the other hand, they store the same integer in my date field (date_tmstmp) as in tstmp field ?

It looks like my date_tmtsmp field doesn't store the right data.

[zonky]

he unix timestamp is the count of seconds since 01.01.1970 - so we have every second another value as timestamp

to compare the timestamp with a date, you should convert the (integer) value to a date

e.g.

SELECT id FROM mm_representations WHERE FROM_UNIXTIME(`date_tmstp`) = '2016-04-24'

pls test your query per phpMyAdmin!

SELECT *, FROM_UNIXTIME(`date_tmstp`) AS 'my_date' FROM mm_representations WHERE 1=1

[charled]

he unix timestamp is the count of seconds since 01.01.1970 - so we have every second another value as timestamp

I know it.

to compare the timestamp with a date, you should convert the (integer) value to a date

I know it too but I'm not trying to compare anything. I just need to store a date for each event (in date_tmstmp but let's call it date_event). Like in an agenda where many events can be the same date. Then I only need to filter by the date in my mm_list.

I'm understanding that Date is not the right attribute because it is a timestamp. I can use a simple text attribute where I can write 24/04/2016 and use it for filtering.

So my question becomes : can I test if user correctly entered the date (like ##/##/#### for example) as I can't use the date picker on a text attribute.

[zonky]

A) I'm understanding that Date is not the right attribute because it is a timestamp. I can use a simple text attribute where I can write 24/04/2016 and use it for filtering.

B) So my question becomes : can I test if user correctly entered the date (like ##/##/#### for example) as I can't use the date picker on a text attribute.

A) you can try a text field.... but how can you compare >, <, between ?

B) at wich "side" => Backend or Frontend?

[charled]

A) you can try a text field.... but how can you compare >, <, between ?

I don't need to compare anything. I'll just have a mm_list with a filter like : "I want all the events of 24-04-2016" (the date will just be coded in the filter in BE ; no MM_filter in FE). With a text attribute this could work if the user in BE enter it with the right format (like '24/04/2016' in french)

B) at wich "side" => Backend or Frontend?

Only in Backend when the user will enter the event's date.

[zonky]

Only in Backend when the user will enter the event's date.

you will filter a list at the backend and use the filter at the header?

can you send a screenshot?