[BUG] DATE('data') or TIMESTAMP('data') does not return null

[guiangumpac]

What is the bug? DATE('data') or TIMESTAMP('data') does not return null

How can one reproduce the bug? Steps to reproduce the behavior:

1. Make a query SELECT DATE('data')
2. See that the output is

"error": {
    "type": "SemanticCheckException",
    "reason": "Invalid Query",
    "details": "date:data in unsupported format, please use yyyy-MM-dd"
  },
  "status": 400

What is the expected behavior? Tableau's TDVT tests expects NULL

Do you have any additional context? I've tried TIMESTAMP('data'), CAST('data' AS DATE), and DATE_FORMAT('date', '%Y-%m-%d') however, they do not return null. This issue affects TDVT test exprtests/standard\setup.string.isdate.txt

[dharminfadia]

Hello Every One

I am trying to change data type using query workbench I want to change data type date to timestamp using query for output schema in other DB but I am not able to do and opensearch having not supporting like cast kind of query any one can help it’s Urgent

query - select timestamp(datecolumn) as date from table

above query is not working plase help !!!

[acarbonetto]

@dharminfadia I'm unable to reproduce your scenario. Here's the query that I attempted:

opensearchsql> SELECT time1 as rawdate, timestamp(time1) as newdate from calcs;                                                         
fetched rows / total rows = 17/17
+---------------------+---------------------+
| rawdate             | newdate             |
|---------------------+---------------------|
| 1970-01-01 19:36:22 | 1970-01-01 19:36:22 |
| 1970-01-01 02:05:25 | 1970-01-01 02:05:25 |
| 1970-01-01 09:33:31 | 1970-01-01 09:33:31 |
| 1970-01-01 22:50:16 | 1970-01-01 22:50:16 |
| null                | null                |
| 1970-01-01 19:57:33 | 1970-01-01 19:57:33 |
| null                | null                |
| 1970-01-01 19:48:23 | 1970-01-01 19:48:23 |
| 1970-01-01 22:20:14 | 1970-01-01 22:20:14 |
| null                | null                |
| 1970-01-01 00:05:57 | 1970-01-01 00:05:57 |
| 1970-01-01 04:40:49 | 1970-01-01 04:40:49 |
| 1970-01-01 04:48:07 | 1970-01-01 04:48:07 |
| null                | null                |
| 1970-01-01 18:58:41 | 1970-01-01 18:58:41 |
| null                | null                |
| 1970-01-01 12:33:57 | 1970-01-01 12:33:57 |
+---------------------+---------------------+

Can you verify the format of the data in datecolumn?

[dharminfadia]

@dharminfadia I'm unable to reproduce your scenario. Here's the query that I attempted:

opensearchsql> SELECT time1 as rawdate, timestamp(time1) as newdate from calcs;                                                         
fetched rows / total rows = 17/17
+---------------------+---------------------+
| rawdate             | newdate             |
|---------------------+---------------------|
| 1970-01-01 19:36:22 | 1970-01-01 19:36:22 |
| 1970-01-01 02:05:25 | 1970-01-01 02:05:25 |
| 1970-01-01 09:33:31 | 1970-01-01 09:33:31 |
| 1970-01-01 22:50:16 | 1970-01-01 22:50:16 |
| null                | null                |
| 1970-01-01 19:57:33 | 1970-01-01 19:57:33 |
| null                | null                |
| 1970-01-01 19:48:23 | 1970-01-01 19:48:23 |
| 1970-01-01 22:20:14 | 1970-01-01 22:20:14 |
| null                | null                |
| 1970-01-01 00:05:57 | 1970-01-01 00:05:57 |
| 1970-01-01 04:40:49 | 1970-01-01 04:40:49 |
| 1970-01-01 04:48:07 | 1970-01-01 04:48:07 |
| null                | null                |
| 1970-01-01 18:58:41 | 1970-01-01 18:58:41 |
| null                | null                |
| 1970-01-01 12:33:57 | 1970-01-01 12:33:57 |
+---------------------+---------------------+

Can you verify the format of the data in datecolumn?

@acarbonetto Hello Sir I am using query bench I think both opensearchsql and querybench both can give a result the issue is I have field name as order.date and feild type is data type is date I want to convert this feild date to timestamp because I want timestamp data . Date is taking only date data How can I achive this using query bench.

[dharminfadia]

@dharminfadia I'm unable to reproduce your scenario. Here's the query that I attempted:

opensearchsql> SELECT time1 as rawdate, timestamp(time1) as newdate from calcs;                                                         
fetched rows / total rows = 17/17
+---------------------+---------------------+
| rawdate             | newdate             |
|---------------------+---------------------|
| 1970-01-01 19:36:22 | 1970-01-01 19:36:22 |
| 1970-01-01 02:05:25 | 1970-01-01 02:05:25 |
| 1970-01-01 09:33:31 | 1970-01-01 09:33:31 |
| 1970-01-01 22:50:16 | 1970-01-01 22:50:16 |
| null                | null                |
| 1970-01-01 19:57:33 | 1970-01-01 19:57:33 |
| null                | null                |
| 1970-01-01 19:48:23 | 1970-01-01 19:48:23 |
| 1970-01-01 22:20:14 | 1970-01-01 22:20:14 |
| null                | null                |
| 1970-01-01 00:05:57 | 1970-01-01 00:05:57 |
| 1970-01-01 04:40:49 | 1970-01-01 04:40:49 |
| 1970-01-01 04:48:07 | 1970-01-01 04:48:07 |
| null                | null                |
| 1970-01-01 18:58:41 | 1970-01-01 18:58:41 |
| null                | null                |
| 1970-01-01 12:33:57 | 1970-01-01 12:33:57 |
+---------------------+---------------------+

Can you verify the format of the data in datecolumn?

@acarbonetto Hello Sir I am using query bench I think both opensearchsql and querybench both can give a result the issue is I have field name as order.date and feild type is data type is date I want to convert this feild date to timestamp because I want timestamp data . Date is taking only date data How can I achive this using query bench.

@acarbonetto

Have you check this thing ?

[acarbonetto]

@dharminfadia you can use timestamp to convert a date to a timestamp. You need to explicitly convert dates to timestamps since the parser doesn't do any automatic conversion/casting.

SELECT date1 as rawdate, timestamp(date1) as newdate from calcs;
fetched rows / total rows = 17/17
+---------------------+---------------------+
| rawdate             | newdate             |
|---------------------+---------------------|
| 2004-04-01 00:00:00 | 2004-04-01 00:00:00 |
| 2004-04-02 00:00:00 | 2004-04-02 00:00:00 |
| 2004-04-03 00:00:00 | 2004-04-03 00:00:00 |
| 2004-04-04 00:00:00 | 2004-04-04 00:00:00 |
...
+---------------------+---------------------+

QueryBench/SQL-CLI should return the same result.

However, there are a couple of datetime issues currently in the SQL plugin - and only a limited number of datetime functions exist in the new parser. If you're getting an error, could you include it? It might be caused by one of these datetime issues.