You are viewing a plain text version of this content. The canonical link for it is here.

Posted to user@cassandra.apache.org by adrien ruffie <ad...@hotmail.fr> on 2019/04/12 06:25:11 UTC

TR: how to store date before > 1970

Hello all,

I have a tricky question about "how to store a date" if dates can be a date prior to 1970 ?
I checked the potential data type and found timestamp and date but both begin to the epoch (January 1, 1970) ...

If I want to store oldest dating, which data type and means I can use ?

Thank you and best regards,

Adrien

RE: how to store date before > 1970

Posted by adrien ruffie <ad...@hotmail.fr>.

Thank Alok,

I checked in Java and Cassandra handle correctly the case.
But in Java following types are need for this behavior:

java.sql.Timestamp
java.math.BigInteger
com.datastax.driver.core.LocalDate (because java.util.Date and java.sql.Date not work correctly ...)

Calendar c = new GregorianCalendar(1157, 10, 11);
ttEntity.setCreationDate(new Timestamp(c.getTimeInMillis()));
ttEntity.setEffectiveDate(LocalDate.fromMillisSinceEpoch(c.getTimeInMillis()));
ttEntity.setTimeLong(BigInteger.valueOf(c.getTimeInMillis()));

Thank you for, your reply.

Best regards,

Adrien
________________________________
De : Alok Dwivedi <al...@instaclustr.com>
Envoyé : vendredi 12 avril 2019 10:15
À : user@cassandra.apache.org
Objet : Re: how to store date before > 1970

You should be able to use timestamp or date for dates before 1970. Here is an example
CREATE TABLE test_ts_date (id int, ts timestamp, dt date, value text, PRIMARY KEY (id, ts));
INSERT INTO test_ts_date (id, ts, dt, value) VALUES (1, '2019-04-11 01:20:30', '2019-04-11', 'value1');
INSERT INTO test_ts_date (id, ts, dt, value) VALUES ( 1, '2014-02-12 03:15:30', '2014-02-12', 'Value3');
INSERT INTO test_ts_date (id, ts, dt, value) VALUES ( 1, '1960-02-12 05:25:33', '1960-02-12', 'Value1 for ts before 1970');
INSERT INTO test_ts_date (id, ts, dt, value) VALUES ( 1, -2522680400000, '1890-22-01', 'Value2 for negative ts');

select * from test_ts_date ;

 id | ts                              | dt         | value
----+---------------------------------+------------+---------------------------
  1 |                  -2522680400000 | 1890-01-22 |    Value2 for negative ts
  1 | 1960-02-12 05:25:33.000000+0000 | 1960-02-12 | Value1 for ts before 1970
  1 | 2014-02-12 03:15:30.000000+0000 | 2014-02-12 |                    Value3
  1 | 2019-04-11 01:20:30.000000+0000 | 2019-04-11 |                    value1


You can choose either of timestamp or date based on what precision you want. They both can take values before 1970. I think there might be issue sending negative long for timestamps from Java driver (I haven’t tried that) but passing dates before 1970 should be fine. Some related tickets for reference.
https://datastax-oss.atlassian.net/browse/JAVA-264
https://datastax-oss.atlassian.net/browse/JAVA-313

Thanks
Alok Dwivedi
Senior Consultant
https://www.instaclustr.com/




On 12 Apr 2019, at 07:25, adrien ruffie <ad...@hotmail.fr>> wrote:

Hello all,

I have a tricky question about "how to store a date" if dates can be a date prior to 1970 ?
I checked the potential data type and found timestamp and date but both begin to the epoch (January 1, 1970) ...

If I want to store oldest dating, which data type and means I can use ?

Thank you and best regards,

Adrien

Re: how to store date before > 1970

Posted by Alok Dwivedi <al...@instaclustr.com>.

You should be able to use timestamp or date for dates before 1970. Here is an example
CREATE TABLE test_ts_date (id int, ts timestamp, dt date, value text, PRIMARY KEY (id, ts));  
INSERT INTO test_ts_date (id, ts, dt, value) VALUES (1, '2019-04-11 01:20:30', '2019-04-11', 'value1');
INSERT INTO test_ts_date (id, ts, dt, value) VALUES ( 1, '2014-02-12 03:15:30', '2014-02-12', 'Value3');
INSERT INTO test_ts_date (id, ts, dt, value) VALUES ( 1, '1960-02-12 05:25:33', '1960-02-12', 'Value1 for ts before 1970');
INSERT INTO test_ts_date (id, ts, dt, value) VALUES ( 1, -2522680400000, '1890-22-01', 'Value2 for negative ts');

select * from test_ts_date ;

 id | ts                              | dt         | value
----+---------------------------------+------------+---------------------------
  1 |                  -2522680400000 | 1890-01-22 |    Value2 for negative ts
  1 | 1960-02-12 05:25:33.000000+0000 | 1960-02-12 | Value1 for ts before 1970
  1 | 2014-02-12 03:15:30.000000+0000 | 2014-02-12 |                    Value3
  1 | 2019-04-11 01:20:30.000000+0000 | 2019-04-11 |                    value1


You can choose either of timestamp or date based on what precision you want. They both can take values before 1970. I think there might be issue sending negative long for timestamps from Java driver (I haven’t tried that) but passing dates before 1970 should be fine. Some related tickets for reference. 
https://datastax-oss.atlassian.net/browse/JAVA-264 <https://datastax-oss.atlassian.net/browse/JAVA-264>
https://datastax-oss.atlassian.net/browse/JAVA-313 <https://datastax-oss.atlassian.net/browse/JAVA-313>

Thanks
Alok Dwivedi
Senior Consultant 
https://www.instaclustr.com/




> On 12 Apr 2019, at 07:25, adrien ruffie <ad...@hotmail.fr> wrote:
> 
> Hello all,
> 
> I have a tricky question about "how to store a date" if dates can be a date prior to 1970 ?
> I checked the potential data type and found timestamp and date but both begin to the epoch (January 1, 1970) ...
> 
> If I want to store oldest dating, which data type and means I can use ?
> 
> Thank you and best regards,
> 
> Adrien