XF 2.0 Two questions about MySQL Queries relevant to NOW() and default CURRENT_TIMESTAMP

[Scandal]

Hello all!
Well, I have two question that have to do with MySQL queries on XF2.
1. Let's say on Setup.php we want to add a new column on a table:

$table->addColumn('datetime', 'datetime')->nullable();

How exact could I apply as default the CURRENT_TIMESTAMP?
It is possible to make it manually via phpmyadmin, but I need to have it ready after initial installation (Setup.php /schemamanager).
Is it possible via a "XF2 way" like ->setDefault(blahblah); or to apply a manual query on the Setup.php separately?
->setDefault('CURRENT_TIMESTAMP'); is not working.

2. I don't need this currently, but occurs during some tests:
Let's say we want to insert a new row on the table with the NOW() datetime on the relevant column.
This example will not work as XF2 query, but as a regular mysql query would be fine:

$db->insert('xf_tablename', [
              'datetime' => 'NOW()',
              'ipaddress' => $ipAddress]);

The issue it the 'NOW()' counted as incorrect. Also if a try the php unixtimestamp and wrote time(), again incorrect.
Any idea?

 

[Tipmoose]

I could be wrong, I'm just starting in on developing add ons for my XF instance...but it seems that most dates in XF are stored as integer via \XF::$time. Using that also seems to ensure the date being entered is in the correct timezone instead of the default UTC. Does that help?

 

[Tipmoose]

The various templates support the standard date display using the integer version of datetime too. However, if you don't want to use \XF::$time, maybe you could use php's

 date('Y-m-d')

That's what I used for one of my tables until I figured out how XF does it everywhere else.

 

[Snog]

XenForo itself stores all dates as a UTC timestamp. But that's not relevant to your question.

From what I know, you can't use the $table->addColumn function to add a datetime column with CURRENT_TIMESTAMP as the default. You would need to use a database query to do that...

$db = $this->db();
$db->query("ALTER TABLE `table_name` ADD `datetime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP");

The second question's answer would be that a DATETIME column expects a text representation of the date. So, you would need to do something like this...

$db->insert('xf_tablename', [
    'datetime' => date("Y-n-d H:i:s",time()),
    'ipaddress' => $ipAddress]);

As usual, I could be wrong. But it's what I feel is correct.