0

I want to export data from a crate database to memsql. The crate database stores its timestamps in milliseconds based on UTC.

Yet when importing this as such to a memsql database, my values get zeroed out.

How should I import my timetamps in memsql?

k0pernikus
  • 60,309
  • 67
  • 216
  • 347

2 Answers2

0

You can insert them as strings in the format:

YYYY-MM-DD HH:mm:ss

Hence, a timestampInMs of 1489582041572 should become: 2017-03-15 12:47:21

In typescript, I created a conversion script for it with the help of moment.js via:

const convertTimestamp = (timestampInMs: number): string => {
    return moment.utc(timestampInMs).format("YYYY-MM-DD HH:mm:ss");
};
k0pernikus
  • 60,309
  • 67
  • 216
  • 347
0

Assuming by "milliseconds based on UTC" you mean the number of milliseconds since the epoch, you can load them into a integer column and then use FROM_UNIXTIME to convert it into a timestamp type (http://docs.memsql.com/docs/from_unixtime - takes in seconds so divide by 1000).

You could either load into a staging table and then insert-select it with FROM_UNIXTIME, or use a computed column to assign the timestamp value when you load the integer value: create table t (mytime_unixtime bigint, mytime as from_unixtime(mytime_unixtime) persisted datetime)

Jack Chen
  • 1,216
  • 1
  • 7
  • 11
  • Crate does indeed use milliseconds, and not seconds. So while this would work, I would have to transform the ms to seconds anyway, and then use that function. This is why I decided to just transform the output in the csv completly to something that I can just LOAD DATA INFILE. – k0pernikus Aug 02 '17 at 18:58