Page 1 of 1

now() <> sysdate

Posted: Thu Oct 30, 2014 2:24 am
by Brett
Hi all,

Can someone explain why these two functions return two different times?

Code: Select all

vertica=> select now(), sysdate;
              now              |          sysdate
-------------------------------+----------------------------
 2014-10-29 16:11:42.624765-07 | 2014-10-29 18:20:17.089996
(1 row)
Thank you

Re: now() <> sysdate

Posted: Thu Oct 30, 2014 1:07 pm
by NorbertKrupa
A quick peak at the documentation would answer your question:
Returns a value of type TIMESTAMP WITH TIME ZONE representing the start of the current transaction.

Re: now() <> sysdate

Posted: Thu Oct 30, 2014 10:21 pm
by Brett
Thanks, Norbert!

However, why would the minutes and seconds be different? I don't think the timezone affects those quantities.

Re: now() <> sysdate

Posted: Fri Oct 31, 2014 1:50 am
by NorbertKrupa
How are you connecting to Vertica?

Re: now() <> sysdate

Posted: Fri Oct 31, 2014 8:36 pm
by Brett
I was running the query in vsql.

Odd, now when I run it today, it seems ok:

Code: Select all

[vertica@prod01 ~]$ vsql
Welcome to vsql, the Vertica Analytic Database interactive terminal.

Type:  \h or \? for help with vsql commands
       \g or terminate with semicolon to execute query
       \q to quit

vertica=> select now(), sysdate;
              now              |          sysdate
-------------------------------+----------------------------
 2014-10-31 12:34:44.686177-07 | 2014-10-31 12:34:44.691116
(1 row)
I don't get it.

Re: now() <> sysdate

Posted: Sun Nov 02, 2014 1:27 am
by NorbertKrupa
Are you 100% sure you just didn't start the session at 16:11 and not check the time until 18:20?

Re: now() <> sysdate

Posted: Mon Nov 17, 2014 9:51 am
by BoMBaY
NOW()

This function returns the start time of the current transaction; the value does not change during the transaction. The intent is to allow a single transaction to have a consistent notion of the "current" time, so that multiple modifications within the same transaction bear the same timestamp.

SYSDATE

This function uses the date and time supplied by the operating system on the server to which you are connected, which must be the same across all servers.