MyBB Community Forums

Hi, I would like ask help about UNIX_TIMESTAMP()

I'm trying a sql code, to move expired threads on date (today is 03/12/2014 then threads until 02/12/2014 should be moved in forum ID 3)

SET mybb_threads.fid='3' WHERE mybb_threads.fid='2' AND mybb_threadfields_data.mytestdate < UNIX_TIMESTAMP()"))

From forum ID 2 to forum ID 3

In mybb_threadfields_data.mytestdate I have date of expiration, for example 15/12/2014

But with this query all threads are moved, also those will expire in future, after 03/12/2014, for example 15/12/2014 etc. These threads have to rest still in forum ID 2.


How can I use < UNIX_TIMESTAMP()")) ?

Tried also with < UNIX_TIMESTAMP(NOW()) but it moves always all threads, also those not yet expired.

Thank you,
sorry for bad English

what is the format of "mytestdate" content? is it a date/time, a long integer (unix timestamp), or something else?

Hi, it's custom regex (Custom Thread Fields - Xthreads):

^(0?[1-9]|[12]\d|3[01])-(0?[1-9]|1[012])-((?:19|20)\d\d)$

I edited it adding "-" for data, I have to edit it in long integer?



Settings for Integer I have only in Underlying Data Type, I try Big Integer?

UNIX_TIMESTAMP() returns a longint like 1351513800

so you need to convert the "mytestdate" result as a timestamp as well.

SET mybb_threads.fid='3' WHERE mybb_threads.fid='2' AND UNIX)TIMESTAMP(mybb_threadfields_data.mytestdate) < UNIX_TIMESTAMP(NOW())")) 

If "mytestdate" looks like '2009-12-08 21:01:33' then you need the above.

Also, there is a dependency on the timezone settings for both PHP/server (what is written in "mytestdate") and what MySQL uses when using the UNIX_TIMESTAMP function.

Unfortunately mytestdate results as dd/mm/yyyy I don't know where I can add manually for example 00:00:00

With

SET mybb_threads.fid='3' WHERE mybb_threads.fid='2' AND UNIX)TIMESTAMP(mybb_threadfields_data.mytestdate) < UNIX_TIMESTAMP(NOW())"))

no effect

neither with

SET mybb_threads.fid='3' WHERE mybb_threads.fid='2' AND UNIX_TIMESTAMP(mybb_threadfields_data.mytestdate) < UNIX_TIMESTAMP(NOW())"))

For timezone server should be ok, because it has my same hour.

Sorry for the typo in the function name.

The function does not need the H: M: S, it can convert most any human readable date just fine. That second query you have, should be the right one, BUT you need to add the join. I missed that you are dealing with two different tables.

So you need to use an UPDATE with JOIN type query.

Hi, in the end more or less now works, I have used:

if ($result = @$mysqli->query("UPDATE mybb_threads 
    LEFT JOIN mybb_threadfields_data
    ON mybb_threads.tid=mybb_threadfields_data.tid
    SET mybb_threads.fid='3' WHERE mybb_threads.fid='2' AND mybb_threadfields_data.mytestdate < UNIX_TIMESTAMP(NOW())"))
 
 {
  echo "Expired threads moved with success.";
}

in input formatter
<?=strtotime({VALUE})?>

and in display format
<?=strftime('%A, %d %B %Y', {VALUE})?>

And expired threads are moved, other threads, not yet expired, remains in old forum, and it's very good.


Only when users choose a date, in newthread template there's date format yyyy/mm/dd (ISO 8601 format?) but when thread is published, I see right date dd/mm/yyyy

Why it takes ISO 8601 format? :/