Support

Account

Home Forums General Issues Query Time Picker field

Solved

Query Time Picker field

  • Hello,

    I have 2 Time-fields which are stored in G:i format in the database. However it seems that they are stored as string in the database because I can’t query if a specific time is between those 2 values.

    Here’s my query:

    $sluitingsdagen = get_posts(
    		array(
    		'numberposts'	=> -1,
    		'post_type'	=> 'her_sluitingsdagen',
    		'meta_query'	=> array(
    		'relation'	=> 'AND',
    				array(	
    					'key'	 	=> 'beginuur',
    					'value'	  	=> date('G:i',$datum),
    					'compare' 	=> '<=',
    				),
    				array(	
    					'key'	 	=> 'einduur',
    					'value'	  	=> date('G:i',$datum),
    					'compare' 	=> '>=',
    				),
    		),
    	));

    What am I doing wrong?

  • Hi @robtje

    Your selected format for the time field does not affect how it’s saved to the DB. From the documentation:

    The value selected can be returned and displayed in different formats but will always be saved to the database as β€˜hh-ii-ss’. This format matches the time part of any datetime value.

    So you’ll need to change your query to that format as well πŸ™‚
    The time values are always stored as strings but if they’re strings that MYSQL recognizes as time/date you can query them as an interval in TIME format: https://codex.wordpress.org/Class_Reference/WP_Query#Custom_Field_Parameters

    May I also suggest that you use WP_Query instead of get_posts. When querying the database in WordPress, you should generally use a WP_Query object. WP_Query objects take a number of useful arguments and do things behind-the-scenes that other database access methods such as get_posts() do not.

  • Hey Jonathan,

    You brought me on the right track. I’m using WP_Query now, and I tried with ‘hh-ii-ss’ format first as you suggested.

    It was still not working, so I went looking in the db myself and saw it was stored as ‘hh:ii:ss’ in my case….

    So this is working now:

    $sluitingsdagen = new WP_Query(
    array(
    'numberposts'	=> -1,
    'post_type'	=> 'her_sluitingsdagen',
    'meta_query'	=> array(
    'relation'	=> 'AND',
    	array(	
    	'key'	 	=> 'hele_dag',
    	'value'	  	=> 'Nee',
    	'compare' 	=> '=',
    	),
    	array(	
    	'key'	 	=> 'beginuur',
    	'value'	  	=> date('H:i:s',$datum),,
    	'compare' 	=> '<=',
    	),
    	array(	
    	'key'	 	=> 'einduur',
    	'value'	  	=> date('H:i:s',$datum),,
    	'compare' 	=> '>=',
    	),
    ),
    ));

    Thanks!

  • Hi @robtje

    Great! I’ll be sure to notify elliot so that he changes the documentation properly. I’ve yet to query based on the new time field myself so I trusted the docs blindly πŸ˜‰

    Best of luck in your project.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic.