We’re pleased to announce ksqlDB 0.20.0!

The 0.20 ksqlDB release includes support for the DATE and TIME data types, along with functionality for working with these types. The DATE type represents a calendar date, and the TIME type represents a time of day in millisecond precision. They are useful for representing less specific time values, such as birthdays or the time of a daily alarm that would not make as much sense as a TIMESTAMP value. The following looks at how these types would be used.

Consider a movie theater company named Franz Theaters that uses ksqlDB to manage movie ticket sales data. They have an Apache Kafka^® topic streaming Avro data with the following format:

{
   "name": "ticket_sales",
   "type": "record",
   "fields": [
      {"name": "purchaser_name", "type": "string"},
      {"name": "movie_title", "type": "string"},
      {"name": "ticket_date", "type": "int", "logicalType": "date"},
      {"name": "showtime", "type": "int", "logicalType": "time-millis"},
      {"name": "purchase_timestamp", "type": "long", "logicalType": "timestamp-millis"},
      {"name": "location", "type": "string"}
   ]
}

Prior to 0.20, the date and time types were interpreted as INTEGER data, but now ksqlDB recognizes the logical types and interprets them as DATE or TIME. Franz Theaters uses the following stream to work with their ticket sales data in ksqlDB:

CREATE STREAM ticket_sales (
  purchaser_name VARCHAR,
  movie_title VARCHAR,
  ticket_date DATE,
  showtime TIME,
  purchase_timestamp TIMESTAMP,
  location VARCHAR
) WITH (
  kafka_topic=’ticket_sales’,
  value_format=’avro’
);

Oh no! Franz Theaters has just been informed that their Mountain View location will have to close on October 11, 2021 from 11 a.m. to 3 p.m. due to an extremely loud road upgrade right outside the theater. To find a list of customers to contact, they use the following query:

> SELECT * FROM ticket_sales
WHERE location = ‘Mountain View’
AND ticket_date = ‘2021-10-11’
AND showtime BETWEEN ‘11:00’ AND ‘15:00’
EMIT CHANGES;
-----------------------------------------------------------------------------------------------------------------
 purchaser_name | movie_title             | ticket_date    | showtime | purchase_timestamp       | location
-----------------------------------------------------------------------------------------------------------------
 Jessica Riley  | Horse And Dog           | 2021-10-11     | 11:00    | 2021-06-31T12:18:39.446  | Mountain View
 Jeremy Gonzales| Hidden In The Mines     | 2021-10-11     | 12:30    | 2021-07-04T13:27:02.210  | Mountain View
 Justin Evans   | Harold                  | 2021-10-11     | 13:00    | 2021-07-04T13:29:45.447  | Mountain View

They plan to use the results of this query to send an automated email to the customers, but they would like to represent the ticket date in a less ambiguous format. To do so, they use the FORMAT_DATE function:

> SELECT
  purchaser_name,
  movie_title,
  FORMAT_DATE(ticket_date, ‘MMMM dd, YYYY’) AS formatted_ticket_date,
  showtime,
  purchase_timestamp,
  location
FROM ticket_sales
WHERE location = ‘Mountain View’
AND ticket_date = ‘2021-10-11’
AND showtime BETWEEN ‘11:00’ AND ‘15:00’;
------------------------------------------------------------------------------------------------------------------------
 purchaser_name | movie_title             | formatted_ticket_date | showtime | purchase_timestamp       | location
------------------------------------------------------------------------------------------------------------------------
 Jessica Riley  | Horse And Dog           | October 11, 2021      | 11:00    | 2021-06-31T12:18:39.446  | Mountain View
 Jeremy Gonzales| Hidden In The Mines     | October 11, 2021      | 12:30    | 2021-07-04T13:27:02.210  | Mountain View
 Justin Evans   | Harold                  | October 11, 2021      | 13:00    | 2021-07-04T13:29:45.447  | Mountain View

After reaching out to all the affected customers, everyone agreed that they would be happy to have their ticket transferred to the same time the following week. To update all of the ticket dates in the stream, they used the following query:

INSERT INTO ticket_sales
SELECT
  purchaser_name,
  movie_title,
  DATEADD(DAYS, 7, ticket_date) AS ticket_date,
  showtime,
  purchase_timestamp,
  location
FROM ticket_sales
WHERE location = ‘Mountain View’
AND ticket_date = ‘2021-10-11’
AND showtime BETWEEN ‘14:00’ AND ‘17:00’;

And on October 18, 2021, everyone happily saw the movie with no loud, distracting road work—all thanks to ksqlDB!

For more information on the DATE and TIME data types, check out the ksqlDB documentation.

Get started with ksqlDB

Get started with ksqlDB today, via the standalone distribution or with Confluent, and join the community to ask a question and find new resources.

Get Started