Kacy Kacy - 8 days ago 4
Node.js Question

Cassandra - Wrong timezone being used when inserting

This is the date I'm trying to insert into my database:

'1970-01-18T00:00:00+00:00'


But when I look at the entry in the database it's 8 hours behind:

1970-01-17 16:00:00


The time zone used on my Ubuntu system is UTC. I checked by typing
date
in the terminal which outputs:

Tue Oct 4 00:00:53 UTC 2016


I'm using node.js, and this is essentially the code being used to insert:

const Cassandra = require( 'cassandra-driver' );
const Promise = require( 'bluebird' );
const db = Promise.promisifyAll( new Cassandra.Client({ contactPoints: ['127.0.0.1'], keyspace: 'project' }) );

let date_created = '1970-01-18T00:00:00+00:00';
db.executeAsync( "INSERT INTO posts (id, date_created) VALUES (?, ?);", [someID, date_created], {prepare: true} );


I installed cassandra straight from the apache website and didn't specify any timezone or anything when installing or executing the binary.

Does anyone know why the time is 8 hours off and how I can fix it?

Edit:
I tried different hardcoded dates, and apparently not all dates are 8 hours behind. Dates associated with months of February, March, and April appear 7 hours behind in the database. December still appears 8 hours behind.

Edit2:
WOW! So I didn't mention the IDE I was using to view my data because I didn't think it was important, but I'm using dbeaver. I decided to look at my data using cqlsh in the terminal (instead of looking at it in dbeaver), and the values are correct! I also tried retrieving the data via node.js and printing out the values, and the values are also correct! This leads me to conclude that there is something up with dbeaver or the cassandra driver that it uses.

Is this a bug? Or is there a way to make the dates display correctly in dbeaver?

Answer

The issue was dbeaver uses jvm under the hood, and it relies on whatever timezone the jvm is using to display dates, which for me is the Pacific timezone since I live in California. This explains the 7/8 hour time difference (depending on daylight savings). The data itself is correct though (dbeaver just alters it for display purposes).

To change which timezone is used to display the dates in dbeaver, you just have to add a couple command line arguments. If you run dbeaver from the terminal, the command is:

path/to/dbeaver.exe -vmargs -Duser.timezone=UTC

To make this simple so you don't have to type that in every time, I suggest creating a desktop shortcut. I'm on a Windows machine, so I just right-clicked on the executable, clicked new > shortcut, right-clicked on the shortcut, clicked properties, and appended -vmargs -Duser.timezone=UTC to the Target command so that it looks like path/to/dbeaver.exe -vmargs -Duser.timezone=UTC. Then I just dragged the shortcut onto the desktop.