From f9b02d68e431dfa08a563c29c68dd1240f1397ed Mon Sep 17 00:00:00 2001
From: Coleman Watts <coleman@civicrm.org>
Date: Thu, 5 May 2022 10:42:20 -0400
Subject: [PATCH] APIv4 - Add NOW() date function

Adds a function for NOW which returns the full date+time, and distinguishes
it from CURDATE which just returns the date part.
---
 Civi/Api4/Query/SqlFunctionCURDATE.php        |  4 +-
 Civi/Api4/Query/SqlFunctionNOW.php            | 41 +++++++++++++++++++
 .../phpunit/api/v4/Action/SqlFunctionTest.php | 26 ++++++++++++
 3 files changed, 70 insertions(+), 1 deletion(-)
 create mode 100644 Civi/Api4/Query/SqlFunctionNOW.php

diff --git a/Civi/Api4/Query/SqlFunctionCURDATE.php b/Civi/Api4/Query/SqlFunctionCURDATE.php
index 951e4498c5..dc843b28ed 100644
--- a/Civi/Api4/Query/SqlFunctionCURDATE.php
+++ b/Civi/Api4/Query/SqlFunctionCURDATE.php
@@ -18,6 +18,8 @@ class SqlFunctionCURDATE extends SqlFunction {
 
   protected static $category = self::CATEGORY_DATE;
 
+  protected static $dataType = 'Date';
+
   protected static function params(): array {
     return [];
   }
@@ -26,7 +28,7 @@ class SqlFunctionCURDATE extends SqlFunction {
    * @return string
    */
   public static function getTitle(): string {
-    return ts('Now');
+    return ts('Today');
   }
 
   /**
diff --git a/Civi/Api4/Query/SqlFunctionNOW.php b/Civi/Api4/Query/SqlFunctionNOW.php
new file mode 100644
index 0000000000..d6764c6363
--- /dev/null
+++ b/Civi/Api4/Query/SqlFunctionNOW.php
@@ -0,0 +1,41 @@
+<?php
+/*
+ +--------------------------------------------------------------------+
+ | Copyright CiviCRM LLC. All rights reserved.                        |
+ |                                                                    |
+ | This work is published under the GNU AGPLv3 license with some      |
+ | permitted exceptions and without any warranty. For full license    |
+ | and copyright information, see https://civicrm.org/licensing       |
+ +--------------------------------------------------------------------+
+ */
+
+namespace Civi\Api4\Query;
+
+/**
+ * Sql function
+ */
+class SqlFunctionNOW extends SqlFunction {
+
+  protected static $category = self::CATEGORY_DATE;
+
+  protected static $dataType = 'Timestamp';
+
+  protected static function params(): array {
+    return [];
+  }
+
+  /**
+   * @return string
+   */
+  public static function getTitle(): string {
+    return ts('Now');
+  }
+
+  /**
+   * @return string
+   */
+  public static function getDescription(): string {
+    return ts('The current date and time.');
+  }
+
+}
diff --git a/tests/phpunit/api/v4/Action/SqlFunctionTest.php b/tests/phpunit/api/v4/Action/SqlFunctionTest.php
index 9da481501c..3641a51a80 100644
--- a/tests/phpunit/api/v4/Action/SqlFunctionTest.php
+++ b/tests/phpunit/api/v4/Action/SqlFunctionTest.php
@@ -245,6 +245,32 @@ class SqlFunctionTest extends UnitTestCase {
     }
   }
 
+  public function testCurrentDate() {
+    $lastName = uniqid(__FUNCTION__);
+    $sampleData = [
+      ['first_name' => 'abc', 'last_name' => $lastName, 'birth_date' => 'now'],
+      ['first_name' => 'def', 'last_name' => $lastName, 'birth_date' => 'now - 1 year'],
+      ['first_name' => 'def', 'last_name' => $lastName, 'birth_date' => 'now - 10 year'],
+    ];
+    Contact::save(FALSE)
+      ->setRecords($sampleData)
+      ->execute();
+
+    $result = Contact::get(FALSE)
+      ->addWhere('last_name', '=', $lastName)
+      ->addWhere('birth_date', '=', 'CURDATE()', TRUE)
+      ->selectRowCount()
+      ->execute();
+    $this->assertCount(1, $result);
+
+    $result = Contact::get(FALSE)
+      ->addWhere('last_name', '=', $lastName)
+      ->addWhere('birth_date', '<', 'DATE(NOW())', TRUE)
+      ->selectRowCount()
+      ->execute();
+    $this->assertCount(2, $result);
+  }
+
   public function testRandFunction() {
     Contact::save(FALSE)
       ->setRecords(array_fill(0, 6, []))
-- 
2.25.1