Discussion Forum

Datetime datatype giving error

Getting error while migrating data using client python:
GraknClientException: [QRY01] Invalid Query Pattern: The class ‘ValueConstraint.String’ cannot be casted to ‘ValueConstraint.DateTime’

in my dataset the date is written as this format: 10/11/2016. With this i understood the correct format should be like: 10/11/2016T22:11:35 but i only want date.
Here i am using datatype ‘datetime’ while writing schema for this. As per the error if i use datatype ‘string’ only then error is:
$Payment has Payment_Date 04/30/2016;$Payment has Payment_Year 2016;$Payment has Payment_Amount $196.35;

mismatched input ‘/’ expecting {’;’, ‘,’} Please check server logs for the stack trace.

Please let me know if i want to use date only not the time, then what should be the correct date format and its datatype.

Also when i changed date format to 04-30-2016 with datatype ‘string’ then error is: $Payment has Payment_Date 04-30-2016;$Payment has Payment_Year 2016;$Payment has Payment_Amount $196.35;

extraneous input ‘-2016’ expecting ‘;’

Hi @Anupriya - see this docs page on attributes.

You have the following options for datetime:

  • yyyy-mm-dd
  • yyyy-mm-ddThh:mm
  • yyyy-mm-ddThh:mm:ss
  • yyyy-mm-ddThh:mm:ss.f
  • yyyy-mm-ddThh:mm:ss.ff
  • yyyy-mm-ddThh:mm:ss.fff

This should allow you to just insert yyyy-mm-dd without adding a time.

Thanks! @daniel for sharing!

I changed the date format to yyyy-mm-dd and used ‘datetime’ datatype then also the error i am getting while migrating dataset using client python is:
grakn.common.exception.GraknClientException: [QRY01] Invalid Query Pattern: The class ‘ValueConstraint.String’ cannot be casted to ‘ValueConstraint.DateTime’. Please check server logs for the stack trace.

Are you sure you aren’t using quotes? Datetime is a value like true or false that doesn’t have to be quoted. Otherwise this could be a bug we haven’t seen before??

No i’m not using any quotes here in date.

can you show the query or code in triple backticks (`) to see more clearly what is being executed?

here is the query part:

def expendture_template(Expenditure):
graql_insert_query = ‘insert $Expenditure isa Expenditure, has Expenditure_Type "’ + Expenditure[“Expenditure_Type”] + ‘"’
if Expenditure[“Travel_Outside_Texas”] == “”:
graql_insert_query += “, has Travel_Outside_Texas false”

else:
    graql_insert_query += ", has Travel_Outside_Texas true"

if Expenditure["Political_Obligation"] == "":
    graql_insert_query += ", has Political_Obligation false"

else:
    graql_insert_query += ", has Political_Obligation true"

if Expenditure["Reimbursement_Intended"] == "":
    graql_insert_query += ", has Reimbursement_Intended false"

else:
    graql_insert_query += ", has Reimbursement_Intended true"

graql_insert_query += ', has Date_Reported "' + Expenditure["Date_Reported"] + '"'
graql_insert_query += ', has Report_Filed "' + Expenditure["Report_Filed"] + '"'
graql_insert_query += ', has Expense_Description "' + Expenditure["Expense_Description"] + '"'
graql_insert_query += ', has Correction "' + Expenditure["Correction"] + '"'
graql_insert_query += ', has TRANSACTION_ID "' + Expenditure["TRANSACTION_ID"] + '"'
graql_insert_query += ', has View_Report "' + Expenditure["View_Report"] + '"'
graql_insert_query += ";"
return graql_insert_query

i had the quotes in some other attributes like userName, removed that too…it’s still not working and giving the error: GraknClientException: [QRY01] Invalid Query Pattern: The class ‘ValueConstraint.String’ cannot be casted to ‘ValueConstraint.DateTime’. Please check server logs for the stack trace.

Can you also print out the graql_insert_query at the end of the function so we can see the query it builds that is causing the error please?

Adding print output, please check if it helps.

Executing Graql Query: insert $Expenditure isa Expenditure, has Expenditure_Type “Political Expenditure from Political Contributions”, has Travel_Outside_Texas false, has Political_Obligation true, has Reimbursement_Intended false, has Date_Reported “2017-01-17”, has Report_Filed “GPAC: General-Purpose Committee Campaign Finance Report”, has Expense_Description “Other | Hosting for Coalition website”, has Correction “”, has TRANSACTION_ID “R20170118090043-F0007”, has View_Report “View Report (http://www.austintexas.gov/edims/document.cfm?id=1269943)”;
Executing Graql Query: insert $Expenditure isa Expenditure, has Expenditure_Type “Unpaid Incurred Obligation”, has Travel_Outside_Texas false, has Political_Obligation true, has Reimbursement_Intended false, has Date_Reported “2017-01-17”, has Report_Filed “GPAC: General-Purpose Committee Campaign Finance Report”, has Expense_Description “Printing Expense | Mailers related to City of Austin District 7 Race”, has Correction “”, has TRANSACTION_ID “R20170118090043-F0009”, has View_Report “View Report (http://www.austintexas.gov/edims/document.cfm?id=1269943)”;
Traceback (most recent call last):
File “migrate.py”, line 116, in
build_finance_graph(inputs=inputs)
File “migrate.py”, line 9, in build_finance_graph
load_data_into_grakn(input, session)
File “migrate.py”, line 19, in load_data_into_grakn
transaction.commit()
File “/opt/anaconda3/lib/python3.8/site-packages/grakn/core/transaction.py”, line 94, in commit
self.execute(transaction_commit_req())
File “/opt/anaconda3/lib/python3.8/site-packages/grakn/core/transaction.py”, line 80, in execute
return self.run_query(request, batch).get()
File “/opt/anaconda3/lib/python3.8/site-packages/grakn/stream/bidirectional_stream.py”, line 125, in get
return self._stream.fetch(self._request_id)
File “/opt/anaconda3/lib/python3.8/site-packages/grakn/stream/bidirectional_stream.py”, line 80, in fetch
raise GraknClientException.of_rpc(e)
grakn.common.exception.GraknClientException: [QRY01] Invalid Query Pattern: The class ‘ValueConstraint.String’ cannot be casted to ‘ValueConstraint.DateTime’. Please check server logs for the stack trace.

used this function to print query output of all templates. For other templates it reached till last print statement “Inserted x items form y into grakn”, but not working for the template consisting date.

def load_data_into_grakn(input, session):
items = parse_data_to_dictionaries(input)

with session.transaction(TransactionType.WRITE) as transaction:
    for item in items:
        graql_insert_query = input["template"](item)
        print("Executing Graql Query: " + graql_insert_query)
        transaction.query().insert(graql_insert_query)
    transaction.commit()

print("\nInserted " + str(len(items)) + " items from [ " + input["data_path"] + "] into Grakn.\n")

If you look at the query that is printed out that is being executed, we can see this:

[...] has Date_Reported “2017-01-17”  [...]

this has quotes right?

within the dataset it don’t have any quotes.

A row of my dataset:
Expenditure_Type,Date_Reported,Report_Filed,Expense_Description,Travel_Outside_Texas,Political_Obligation,Reimbursement_Intended,Correction,View_Report,TRANSACTION_ID
Political Expenditure from Political Contributions,2016-07-18,SPAC: Specific-Purpose Committee Campaign Finance Report,Food/Beverage Expense | Volunteer Food,Yes,View Report (http://www.austintexas.gov/edims/document.cfm?id=1258188),R20160718161156-F0001

Where i am wrong with the dataset?

yeah but in your query building template you’re adding the strings in - if you remove them then it shouldn’t be an issue

can you please elaborate, i didn’t understood the problem?

The query you build via your templates, as you printed out when I asked you to, shows this output that will be sent to the server:

insert $Expenditure isa Expenditure, has Expenditure_Type “Political Expenditure from Political Contributions”, has Travel_Outside_Texas false, has Political_Obligation true, has Reimbursement_Intended false, has Date_Reported “2017-01-17”, has Report_Filed “GPAC: General-Purpose Committee Campaign Finance Report”, has Expense_Description “Other | Hosting for Coalition website”, has Correction “”, has TRANSACTION_ID “R20170118090043-F0007”, has View_Report “View Report (http://www.austintexas.gov/edims/document.cfm?id=1269943)”;

this is what the output of your python function is.

If you see this output, you can see that it contains this clause: [...] has Date_Reported “2017-01-17” [...]
well - this is incorrectly generated. Because it sohuld never have included quotes - if the database server sees quotes around attribute values, it treats them as strings. This is not what you want.

From the template function shown above how could i remove these quotes?

@Anupriya would recommend working on it and coming back with what you tried, and a solution if you figured it out.

If still having trouble, we can jump in and help.

yeah sure…thanks for help!