Cassandra, how to compare field to current Date

Question

I am new using Cassandra, I have been looking for information but I do not finish solving the doubts.

First I have a .csv file from where I load the data of the last players of the football world wide cup.

My csv file has the following structure:

Group;Team;Number;Position;Name;Day;Month;Year;Club;Liga;Captain        
A;Brasil;1;PORTERO;Jefferson;2;1;1983;Botafogo;Brasil;NO    
A;Brasil;2;DEFENSA;Dani Alves;6;5;1983;FC Barcelona;Espana;NO   
A;Brasil;3;DEFENSA;Thiago Silva (c);22;9;1984;Paris Saint-Germain;Brasil;NO

Based on the data I created the following statement to generate my new table:

CREATE TABLE players (
    group text,
    team text,
    number int,
    position text,
    name text,
    day int,
    month int,
    year int,
    club text,
    liga text,
    captain text,
    PRIMARY key (name)
);

Here I have my first question, what I have done is to create a int field for each of the data, day, month and year. Would it be possible to encapsulate those three data in a format, for example DATE? So for future calculations, do not have to do them one at a time.

I would like to know if those three fields in the table could be encapsulated in a date format, let's call it DATE or something similar.

Here is my second doubt; If I, for example, would like to get the older player, I had been thinking of comparing the current date with day, month, year fields of the players of the different teams that I own, here is the problem, since I can not find in the documentation how to perform this type of query and how to compare three data in my table with some function that returns to me the current date.

Could you add a sample SELECT query for your second question? It doesn't have to be syntactical correct, just would serve an example. I'm not sure I understand what you would like to do. — Oresztesz

Oresztesz Oresztesz · Accepted Answer · 2017-12-14T14:02:06

I can give you answer to your first question. You can use DATE data type starting from Cassandra 3.0.

CREATE TABLE players (
    group text,
    team text,
    number int,
    position text,
    name text,
    birth_date date,
    club text,
    liga text,
    captain text,
    PRIMARY key (name)
);

Sample CSV will look like the snippet below:

Group;Team;Number;Position;Name;Birth_Date;Year;Club;Liga;Captain
A;Brasil;1;PORTERO;Jefferson;1983-01-02;Botafogo;Brasil;NO
A;Brasil;2;DEFENSA;Dani Alves;1983-05-06;FC Barcelona;Espana;NO
A;Brasil;3;DEFENSA;Thiago Silva (c);1984-09-22;Paris Saint-Germain;Brasil;YES

You can load the CSV into table definition using the following command:

COPY players (group, team, number, position, name, birth_date, club, liga, captain) 
FROM './players.csv'
WITH HEADER = 'true' AND DELIMITER = ';';

Cassandra, how to compare field to current Date

1 Answers