How to compare datetime field with current datetime

General Discussions - English
1

Hi community,

I cannot compare a datetime field with current datetime.
I do
SELECT *,NOW() AS date_now FROM idx_fulltext WHERE MATCH(‘openai"’) AND date_publi_unix_int<date_now ORDER BY date_publi_unix_int DESC LIMIT 200;

What i want is to retrieve datas where the date (date_publi_unix_int ) is not after today.
is there way to do that ?
I have used UNIX_TIMESTAMP(_DATE_ADDED) as date_publi_unix_int syntax in the index creation command file.

Thanks guys !

2

there are lot of data function and you could use YEARMONTHDAY or any other functions there

3

Thanks but i still have errors :
Erreur SQL (1064) : sphinxql: syntax error, unexpected ‘(’ near '( date_publi_unix_int ) < date_now ORDER BY date_publi_unix_int DESC LIMIT 200’uery

My query :
SELECT *,yearmonthday(NOW()) AS date_now FROM idx_fulltext WHERE MATCH(‘openai"’) AND YEARMONTHDAY( date_publi_unix_int ) < date_now ORDER BY date_publi_unix_int DESC LIMIT 200;

4

it could be better to move all expression at the select list and use alias at filter \ grouper

5

In general I would say just compute ‘now’ outside of manticore, eg in PHP

<?php
$now = time();
$sql = "SELECT *, FROM idx_fulltext 
        WHERE MATCH('openai') AND date_publi_unix_int < $now 
        ORDER BY date_publi_unix_int DESC LIMIT 200";

If want it midnight etc.

$today = strtotime(date('Y-m-d'));

As manticores ‘expression’ syntax in ‘WHERE’ is limited.

6

Thanks guys, it seems that i have to computed the current timestamp outside the SQL prompt.
And if i do so it work great !