[SOLVED] How to cast a timestamp to date in format YYYY-MM-DD in griddb

How to cast a timestamp to date in format YYYY-MM-DD in griddb

I created a collection container name ‘orders’ with the below DDL using NewSQL Interface:

CREATE TABLE orders 
(
    orderId STRING PRIMARY KEY,
    customerId STRING,
    orderDate TIMESTAMP,
    totalAmount DOUBLE,
    status STRING
);

The values in the column orderDate are in timestamp format. How do I extract date from a TIMESTAMP as when I use CAST(ord.orderDate AS date) as orderDate, I am getting an error

Invalid Query

However when I am using the below query extracting the year, month and date and concatenating them it worked but its returning a string but how to return a date?

SELECT
    EXTRACT(YEAR, ord.orderDate)||'-'||EXTRACT(MONTH, ord.orderDate)||'-'||EXTRACT(DAY, ord.orderDate) AS order_date,
    COUNT(ord.orderID) AS order_count
FROM
    orders ord
GROUP BY
    order_date;

Solution

You can use the STRFTIME function to convert timestamp to a string according to the specified format (see the attached link for full reference on the format):

SELECT
    STRFTIME('%Y-%m-%d', ord.orderDate) AS order_date,
    COUNT(ord.orderID) AS order_count
FROM
    orders ord
GROUP BY
    order_date;