Results 1 to 12 of 12

Thread: How to filter on a date ?

  1. #1
    User
    Join Date
    04-01-10.
    Posts
    292

    Default How to filter on a date ?

    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…
    Last edited by charled; 03/30/2016 at 01:58.

  2. #2
    User zonky's Avatar
    Join Date
    11-29-11.
    Location
    Berlin
    Posts
    160

    Default

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

    look at "Filterung nach Datum"

  3. #3
    User
    Join Date
    04-01-10.
    Posts
    292

    Default

    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 ?

  4. #4
    User zonky's Avatar
    Join Date
    11-29-11.
    Location
    Berlin
    Posts
    160

    Default

    all values saved as unix timestamp at database...

    pls post your SQL qyuery

  5. #5
    User
    Join Date
    04-01-10.
    Posts
    292

    Default

    I've tried many possibilities :
    Code:
    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 ?
    Last edited by charled; 03/30/2016 at 16:08.

  6. #6
    User zonky's Avatar
    Join Date
    11-29-11.
    Location
    Berlin
    Posts
    160

    Default

    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

  7. #7
    User
    Join Date
    04-01-10.
    Posts
    292

    Default

    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.

  8. #8
    User zonky's Avatar
    Join Date
    11-29-11.
    Location
    Berlin
    Posts
    160

    Default

    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
    Last edited by zonky; 03/30/2016 at 16:48.

  9. #9
    User
    Join Date
    04-01-10.
    Posts
    292

    Default

    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.
    Last edited by charled; 03/30/2016 at 17:31.

  10. #10
    User zonky's Avatar
    Join Date
    11-29-11.
    Location
    Berlin
    Posts
    160

    Default

    Quote Originally Posted by charled View Post
    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?

  11. #11
    User
    Join Date
    04-01-10.
    Posts
    292

    Default

    Quote Originally Posted by zonky View Post
    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.

  12. #12
    User zonky's Avatar
    Join Date
    11-29-11.
    Location
    Berlin
    Posts
    160

    Default

    Quote Originally Posted by charled View Post
    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?

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •