now() <> sysdate

Moderator: NorbertKrupa

Post Reply
Brett
Beginner
Beginner
Posts: 49
Joined: Fri Oct 11, 2013 1:19 am

now() <> sysdate

Post by Brett » Thu Oct 30, 2014 2:24 am

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

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: now() <> sysdate

Post by NorbertKrupa » Thu Oct 30, 2014 1:07 pm

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.
Checkout vertica.tips for more Vertica resources.

Brett
Beginner
Beginner
Posts: 49
Joined: Fri Oct 11, 2013 1:19 am

Re: now() <> sysdate

Post by Brett » Thu Oct 30, 2014 10:21 pm

Thanks, Norbert!

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

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: now() <> sysdate

Post by NorbertKrupa » Fri Oct 31, 2014 1:50 am

How are you connecting to Vertica?
Checkout vertica.tips for more Vertica resources.

Brett
Beginner
Beginner
Posts: 49
Joined: Fri Oct 11, 2013 1:19 am

Re: now() <> sysdate

Post by Brett » Fri Oct 31, 2014 8:36 pm

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.

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: now() <> sysdate

Post by NorbertKrupa » Sun Nov 02, 2014 1:27 am

Are you 100% sure you just didn't start the session at 16:11 and not check the time until 18:20?
Checkout vertica.tips for more Vertica resources.

User avatar
BoMBaY
Beginner
Beginner
Posts: 26
Joined: Tue Jul 16, 2013 5:45 am

Re: now() <> sysdate

Post by BoMBaY » Mon Nov 17, 2014 9:51 am

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.
Itipong Chewinpipat (Bay)
DBA Specialist (Vertica/Oracle)

Image
ImageImage

Post Reply

Return to “Vertica SQL Functions”