From user-return-63663-archive-asf-public=cust-asf.ponee.io@cassandra.apache.org Fri Apr 12 13:21:25 2019 Return-Path: X-Original-To: archive-asf-public@cust-asf.ponee.io Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [207.244.88.153]) by mx-eu-01.ponee.io (Postfix) with SMTP id E0CD0180621 for ; Fri, 12 Apr 2019 15:21:24 +0200 (CEST) Received: (qmail 85110 invoked by uid 500); 12 Apr 2019 13:14:13 -0000 Mailing-List: contact user-help@cassandra.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@cassandra.apache.org Delivered-To: mailing list user@cassandra.apache.org Received: (qmail 85094 invoked by uid 99); 12 Apr 2019 13:14:13 -0000 Received: from pnap-us-west-generic-nat.apache.org (HELO spamd3-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 12 Apr 2019 13:14:13 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd3-us-west.apache.org (ASF Mail Server at spamd3-us-west.apache.org) with ESMTP id 23CF018101A for ; Fri, 12 Apr 2019 13:14:13 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd3-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: 2.499 X-Spam-Level: ** X-Spam-Status: No, score=2.499 tagged_above=-999 required=6.31 tests=[HTML_MESSAGE=2, KAM_NUMSUBJECT=0.5, RCVD_IN_DNSWL_NONE=-0.0001, SPF_HELO_PASS=-0.001, SPF_PASS=-0.001, URIBL_BLOCKED=0.001] autolearn=disabled Received: from mx1-lw-eu.apache.org ([10.40.0.8]) by localhost (spamd3-us-west.apache.org [10.40.0.10]) (amavisd-new, port 10024) with ESMTP id HZRwhvYCGX_z for ; Fri, 12 Apr 2019 13:14:09 +0000 (UTC) Received: from EUR02-HE1-obe.outbound.protection.outlook.com (mail-oln040092068047.outbound.protection.outlook.com [40.92.68.47]) by mx1-lw-eu.apache.org (ASF Mail Server at mx1-lw-eu.apache.org) with ESMTPS id 498C85F3ED for ; Fri, 12 Apr 2019 13:04:46 +0000 (UTC) Received: from HE1EUR02FT029.eop-EUR02.prod.protection.outlook.com (10.152.10.53) by HE1EUR02HT014.eop-EUR02.prod.protection.outlook.com (10.152.10.76) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384) id 15.20.1771.16; Fri, 12 Apr 2019 13:04:40 +0000 Received: from VI1PR01MB2941.eurprd01.prod.exchangelabs.com (10.152.10.54) by HE1EUR02FT029.mail.protection.outlook.com (10.152.10.147) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384) id 15.20.1771.16 via Frontend Transport; Fri, 12 Apr 2019 13:04:40 +0000 Received: from VI1PR01MB2941.eurprd01.prod.exchangelabs.com ([fe80::985:2acc:5305:e33e]) by VI1PR01MB2941.eurprd01.prod.exchangelabs.com ([fe80::985:2acc:5305:e33e%2]) with mapi id 15.20.1792.009; Fri, 12 Apr 2019 13:04:40 +0000 From: adrien ruffie To: "user@cassandra.apache.org" Subject: RE: how to store date before > 1970 Thread-Topic: how to store date before > 1970 Thread-Index: AQHU8EB8yRTU27KuO0CIsJzzb0CnvKY4EFfQgAAe4ACAAFAKag== Date: Fri, 12 Apr 2019 13:04:40 +0000 Message-ID: References: ,<7915BC83-974E-4E76-BB00-A03B29839A5E@instaclustr.com> In-Reply-To: <7915BC83-974E-4E76-BB00-A03B29839A5E@instaclustr.com> Accept-Language: fr-FR, en-US Content-Language: fr-FR X-MS-Has-Attach: X-MS-TNEF-Correlator: x-incomingtopheadermarker: OriginalChecksum:056D37FCD49EED53F543E7269159BB31B4D2D1E9ED2F31FD45BB96F6F5710B7E;UpperCasedChecksum:C4B2A27DE9F6AAC8AAAFF78EC4F25965F3CFFD8035D3BB02F7A0D7D96D29118F;SizeAsReceived:6928;Count:43 x-ms-exchange-messagesentrepresentingtype: 1 x-tmn: [ml1MAUyHImikGi4LeP+5QTtMm3Di/hwV] x-ms-publictraffictype: Email x-incomingheadercount: 43 x-eopattributedmessage: 0 x-microsoft-antispam: BCL:0;PCL:0;RULEID:(2390118)(7020095)(20181119110)(201702061078)(5061506573)(5061507331)(1603103135)(2017031320274)(2017031323274)(2017031324274)(2017031322404)(1603101475)(1601125500)(1701031045);SRVR:HE1EUR02HT014; x-ms-traffictypediagnostic: HE1EUR02HT014: x-ms-exchange-purlcount: 3 x-microsoft-antispam-message-info: qfHxcQcM1ZESIsFJT4TwM1sIpa7TLcMwWvXaj0pTjTiet3BbisCXuXq/UD8DVIlFH0Cv8kL+TCLR1vr24gpkQh3o9rwIexHWov85M8G+jJiPuLYCSdIJmunPpGJJTcbKNEbl4ND5E/Tketdkho52QQAI9R+Vc05ILJJXJGyiP+F9eDAmoAyrqtr8ZQ8bNX6/ Content-Type: multipart/alternative; boundary="_000_VI1PR01MB29414DC0F1C633E61C4C0EE1A9280VI1PR01MB2941eurp_" MIME-Version: 1.0 X-OriginatorOrg: outlook.com X-MS-Exchange-CrossTenant-RMS-PersistedConsumerOrg: 00000000-0000-0000-0000-000000000000 X-MS-Exchange-CrossTenant-Network-Message-Id: d67e3786-1608-436b-2e32-08d6bf476c68 X-MS-Exchange-CrossTenant-rms-persistedconsumerorg: 00000000-0000-0000-0000-000000000000 X-MS-Exchange-CrossTenant-originalarrivaltime: 12 Apr 2019 13:04:40.1563 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Internet X-MS-Exchange-CrossTenant-id: 84df9e7f-e9f6-40af-b435-aaaaaaaaaaaa X-MS-Exchange-Transport-CrossTenantHeadersStamped: HE1EUR02HT014 --_000_VI1PR01MB29414DC0F1C633E61C4C0EE1A9280VI1PR01MB2941eurp_ Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable 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.Dat= e not work correctly ...) Calendar c =3D 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 Envoy=E9 : vendredi 12 avril 2019 10:15 =C0 : 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, PRIMA= RY KEY (id, ts)); INSERT INTO test_ts_date (id, ts, dt, value) VALUES (1, '2019-04-11 01:20:3= 0', '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, '1= 890-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 1= 970 1 | 2014-02-12 03:15:30.000000+0000 | 2014-02-12 | Val= ue3 1 | 2019-04-11 01:20:30.000000+0000 | 2019-04-11 | val= ue1 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 sendi= ng negative long for timestamps from Java driver (I haven=92t tried that) b= ut passing dates before 1970 should be fine. Some related tickets for refer= ence. 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 > 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 beg= in 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 --_000_VI1PR01MB29414DC0F1C633E61C4C0EE1A9280VI1PR01MB2941eurp_ Content-Type: text/html; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable
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.Dat= e not work correctly ...)

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

Thank you for, your reply.

Best regards,

Adrien
De : Alok Dwivedi <alok.= dwivedi@instaclustr.com>
Envoy=E9 : vendredi 12 avril 2019 10:15
=C0 : user@cassandra.apache.org
Objet : Re: how to store date before > 1970
 
You should be able to use timestamp or date for dates befor= e 1970. Here is an example
CREATE TABLE test_ts_date (id int, ts timestamp, dt date, v= alue text, PRIMARY KEY (id, ts));  
INSERT INTO test_ts_date (id, ts, dt, value) VALUES (1, '20= 19-04-11 01:20:30', '2019-04-11', 'value1');
INSERT INTO test_ts_date (id, ts, dt, value) VALUES ( 1, '2= 014-02-12 03:15:30', '2014-02-12', 'Value3');
INSERT INTO test_ts_date (id, ts, dt, value) VALUES ( 1, '1= 960-02-12 05:25:33', '1960-02-12', 'Value1 for ts before 1970');
INSERT INTO test_ts_date (id, ts, dt, value) VALUES ( 1, -2= 522680400000, '1890-22-01', 'Value2 for negative ts');

selec= t * from test_ts_date ;

 = ;id | ts          &= nbsp;                   | dt         | value
----&= #43;---------------------------------+------------+----------------= -----------
 = ; 1 |         = ;         -2522680400000<= /b> | 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 pr= ecision you want. They both can take values before 1970. I think there migh= t be issue sending negative long for timestamps from Java driver (I haven= =92t tried that) but passing dates before 1970 should be fine. Some related tickets for reference. 
h= ttps://datastax-oss.atlassian.net/browse/JAVA-264
h= ttps://datastax-oss.atlassian.net/browse/JAVA-313

Thanks
Alok Dwivedi
Senior Consultant 




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

Hello all,

I have a tricky question about "how to store a date" if d= ates 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

--_000_VI1PR01MB29414DC0F1C633E61C4C0EE1A9280VI1PR01MB2941eurp_--