Query Time Picker field

• robtje

• September 20, 2016 at 5:50 pm

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?

• Jonathan

• September 20, 2016 at 6:12 pm

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.

• robtje

• September 20, 2016 at 7:14 pm

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!

• Jonathan

• September 22, 2016 at 5:56 pm

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.