Bâtissez votre pont vers le cloud en temps réel avec la plateforme Confluent 7.0 et Cluster Linking | Lire le blog

Announcing ksqlDB 0.20.0

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.

DATE and TIME data types ksqlDB

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

Chittaranjan Prasad is an engineer on the ksqlDB team at Confluent. He joined in 2020 after completing his bachelor’s degree in computer science and economics at the University of California, Berkeley.

Did you like this blog post? Share it now

Subscribe to the Confluent blog

More Articles Like This

A Guide to Stream Processing and ksqlDB Fundamentals

Event streaming applications are a powerful way to react to events as they happen and to take advantage of data while it is fresh. However, they can be a challenge

How to Efficiently Subscribe to a SQL Query for Changes

Imagine that you have real-time data about what’s happening in the stock market, and you want to support a large number of customized dashboards displaying the data as it comes

Announcing ksqlDB 0.22.0

We’re pleased to announce ksqlDB 0.22.0! This release includes source streams and source tables as well as improved pull query (for key-range predicates) and push query performance. All of these