configuring and Understanding current timestamps /local timestamps

Sam_2

Hi, I'm going through Guidebook trying to understand how the timestamps are determined, but cannot find anything concrete. Our Vertica instance was simply "installed out of the box" So I do: select localtimestamp - which yields 2013-05-08 16:46:24.657677 but when doing select current_timestamp, it yields 2013-05-08 15:46:24.657677 The local timestamp (16:46) is correct and what we want. What do i need to configure things to get me the right time? More importantly is there documentation pointing this stuff out and explaining it from an Administrator's Guide. Also wondering if Vertica didn't adjust for daylight savings or what. Or we just misconfigured things from the start Thanks

[Deleted User]

H Sam, by any chance have you yet found the following page in our documentation?: https://my.vertica.com/docs/CE/6.0.1/HTML/index.htm#12584.htm In short, Vertica inherits time zone settings from the underlying operating system. There are a few more specifics on that page, including details about how to explicitly set a time zone; but I would recommend that you take a look through your Linux distribution's settings and documentation and make sure that it's configured properly first. For example, the command-line command "date" should print out the correct time and time zone. (Note that Vertica may not pick up OS-level timezone changes until you shut down and re-start the server.)

Sam_2

Thanks Adam - I didn't even know about the online docs - been relying on the PDFs, but the online docs looks cleaner. So i realized where I got confused, **I think**...i had two sessions open. So I guess Session A I may have opened recently and shows the current timestamp. Session B I had opened the session 1hr before and shows the timestamp from there....coincidentally...an hr apart. current_timestamp, now(), localtimestamp - all seem to be indicative of when the session logged in. The only time it changes when I give an explicit commit. At least this is what I see in vsql right now as I putza around. Is this the normal behavior? thanks

[Deleted User]

Hi Sam, yes, it's normal that the timestamp is the current session timestamp. Or, more specifically, it's the current transaction timestamp; if you issue a COMMIT, you should get an updated timestamp. This is for consistency -- if you run a script that executes a bunch of statements that depend on current_timestamp, the value of that timestamp won't drift as the script executes. It also allows Vertica to perform a number of substantial optimizations; if we know that a value is never going to change, we only need to generate it once rather than for every row; and in some cases this simplifies the query and allows our optimizer to perform further optimizations. If you really want the current time, you can use SELECT CLOCK_TIMESTAMP();

Sam_2

Got it and Isee what Vertica is trying to do. I guess I was expecting behavior similar to when a connection opens to a relational DB (mysql, sql server, etc). Thanks for the responses and the link to the documentation. I'll try relying on that instead of the PDFs. btw - the website is much more usable than the old site.