MySQL NOW() vs. Vertica NOW()

[JimKnicely]

Hi,

If you’ve been using MySQL for a while you are probably familiar with the NOW() date function which returns the current date and time.

Code: Select all

MySQL> SELECT now();
+---------------------+
| now()               |
+---------------------+
| 2013-02-07 07:03:49 |
+---------------------+
1 row in set (0.00 sec)

MySQL> SELECT now();
+---------------------+
| now()               |
+---------------------+
| 2013-02-07 07:03:51 |
+---------------------+
1 row in set (0.00 sec)

MySQL> SELECT now();
+---------------------+
| now()               |
+---------------------+
| 2013-02-07 07:03:54 |
+---------------------+
1 row in set (0.00 sec)

Notice how in MySQL the time of each call to the NOW() function changes staying in line with actual system time.

In Vertica, the NOW() function also exists. However, it has a very different purpose than in MySQL. In Vertica the NOW() function returns a value of type TIMESTAMP WITH TIME ZONE representing the start of the current transaction.

Code: Select all

dbadmin=> SELECT now();
              now
-------------------------------
2013-02-07 07:03:11.744232-05
(1 row)

dbadmin=> SELECT now();
              now
-------------------------------
2013-02-07 07:03:11.744232-05
(1 row)

dbadmin=> SELECT now();
              now
-------------------------------
2013-02-07 07:03:11.744232-05
(1 row)

Notice that in Vertica the time does not change with each call to the NOW() function! This is because each of the SELECT statements are part of the same database transaction (Note: The first SELECT began the transaction).

In Vertica the STATEMENT_TIMESTAMP() function is analogous to the MySQL NOW() function:

Code: Select all

dbadmin=> SELECT statement_timestamp();
      statement_timestamp
-------------------------------
2013-02-07 07:17:14.162963-05
(1 row)

dbadmin=> SELECT statement_timestamp();
      statement_timestamp
-------------------------------
2013-02-07 07:17:15.670698-05
(1 row)

dbadmin=> SELECT statement_timestamp();
      statement_timestamp
-------------------------------
2013-02-07 07:17:18.018581-05
(1 row)

Have fun!