Forum: Building VoltDB Applications

Post: Adhoc query and timestamp

Adhoc query and timestamp
hayatoa
Dec 5, 2011
For testing purpose, I want to make adhoc query with timestamp in where clause like following...

VoltTable[] results = myApp.callProcedure("@AdHoc","SELECT * FROM sample where timestamp >= '2011-12-06 01:55:00' order by timestamp asc limit 1000").getResults();

However database treats timestamp as '2011-12-06 10:55:00' instead of '2011-12-06 01:55:00' even I set database to ignore timezone as below.

CREATE TABLE sample(
timestamp timestamp without time zone not null
);
Output is below.
2011-12-06T10:55:00.000.0
2011-12-06T10:55:00.000.0
2011-12-06T10:55:00.000.0
2011-12-06T10:55:00.000.0
2011-12-06T10:55:00.000.0


The server timezone in which voltdb is installed is JPN(Japan).
It seems to be voltdb tries to understand timestamp as UTC and convert it to JPN. By the way, UTC is "JPN - 9 hours".

What is happening on timestamp?

Is there any configuration that I can set timezone of voltdb?

Hayato
Query with tmestamp in where clause issue :
chandru
Jan 3, 2012
I would like to declare one of the field as timestamptype which should be used to query results from the table.

E.g.
CREATE TABLE ITEM (
ITEMID INTEGER DEFAULT '0' NOT NULL,
ITEMNAME VARCHAR(128) DEFAULT NULL,
STARTTIME TIMESTAMP DEFAULT NULL,
ENDTIME TIMESTAMP DEFAULT NULL,
PRIMARY KEY (ITEMID)
);


I have written a stored procedure to retrieve results from ITEM table by adding starttime and endtime in where clause.

Example : select * from ITEM where STARTIME >= ? and ENDTIME <= ?

It is not returning expected results. can you please any one guide me.
I am new to VOLTDB(NOSQL DB) and trying to see VOLT DB works for my requirement.

Thanks
Chandru
Hi Chandru, I tried to
aweisberg
Jan 4, 2012
Hi Chandru,

I tried to quickly reproduce your result here and didn't have any luck. I tests and both boundry conditions do the right thing. Can you send your application to support@voltdb.com so I can reproduce it?

Thanks,
Ariel