3

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?

Kacy
  • 3,330
  • 4
  • 29
  • 57
  • are you inserting the time via node or is it being stamped by cassandra – Derek Pollard Oct 04 '16 at 00:04
  • @derek I'm inserting the time from node (as shown in the code above). – Kacy Oct 04 '16 at 00:30
  • what is the database type for date_created column ? try 1970-01-18T00:00:00+0000 instead of 1970-01-18T00:00:00+00:00 – s7vr Oct 04 '16 at 06:21
  • Have you tried to insert a Date object, instead of the string to be parsed as datetime?, in other words, you can try with `let date_created = new Date(Date.parse("1970-01-18T00:00:00+00:00"))` – Carlos Monroy Nieblas Oct 04 '16 at 06:36
  • @Reddy The date_created column is of type `timestamp`. Trying your suggestion achieved the same result. – Kacy Oct 04 '16 at 08:58
  • @CarlosMonroyNieblas I just tried it, and unfortunately it didn't change anything. – Kacy Oct 04 '16 at 08:59
  • what timezone is your dbeaver utility is running? it must have tried to convert the UTC time to that timezone which makes sense and btw the diff in the offset between dates is because of day light savings. – s7vr Oct 04 '16 at 09:29

2 Answers2

0

Cassandra timestamp represents a single moment in time and does not store timezone information, the correct representation in JavaScript is Date.

When converted to string, the Date instance shows the date and time according to the timezone of the system (the system running Node.js).

In your case:

const d1 = new Date('1970-01-18T00:00:00+00:00');
// Numeric value as the number of milliseconds since UNIX epoch.
const time = d1.getTime();
db.executeAsync(insertQuery, [ id, d1 ], { prepare: true } );
// ...
db.executeAsync(selectQuery, [ id ], { prepare: true })
  .then((result) => {
    console.log('Are equal?', time, result.first()['date_created'].getTime())
  })

The 2 values should be equal.

jorgebg
  • 6,560
  • 1
  • 22
  • 31
  • I used a Date object just like you did, and unfortunately it didn't change the result in the database. I decided to change the date to different hardcoded values just in case I was missing something obvious, and I got strange behavior where some months appear 7 hours behind instead of 8. I added an Edit to my question. – Kacy Oct 04 '16 at 08:52
  • Btw when I queried back the timestamps in node, the timezone `+0000` was included. – Kacy Oct 04 '16 at 14:39
0

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.

Kacy
  • 3,330
  • 4
  • 29
  • 57