DEV Community

Judy
Judy

Posted on

2 1 1 1 1

Find Missing Datetimes from a CSV-format Time Series with Fixed Interval #eg75

Problem description & analysis

Below is part of the data in CSV file csv.csv:

2021-02-02 00:00:00

2021-02-02 01:00:00

2021-02-02 02:00:00

2021-02-02 03:00:00

2021-02-02 04:00:00

2021-02-02 05:00:00

2021-02-02 06:00:00

2021-02-02 07:00:00

2021-02-02 08:00:00

2021-02-02 10:00:00

...

The whole data is from 2021-02-02 00:00:00 to 2021-03-03 23:00:00 and has been ordered at an interval of one hour. There are altogether 30 days of data and, in theory, 24 records each day. Actually, there are certain records are left out. Our task is to find those missing records. Below is the desired result:

2021-02-02 09:00:00

2021-02-02 14:00:00

2021-02-03 06:00:00

2021-02-03 09:00:00

2021-02-03 11:00:00

2021-02-03 17:00:00

2021-02-03 18:00:00

2021-02-03 19:00:00

2021-02-03 20:00:00

2021-02-03 23:00:00

...

Solution & explanation

Write the following script p1.dfx in esProc script:

Image description
Explanation:

A1  List all dates and times between 2021-02-02 00:00:00 and 2021-03-03 23:00:00 at the interval of one hour.

A2  Import the date and time data from the CSV file.

A3  Calculate difference between A1 and A2.

Read How to Call an SPL Script in Java to learn how to integrate the script code into a Java program.

Open source SPL source address

Download

Sentry image

Hands-on debugging session: instrument, monitor, and fix

Join Lazar for a hands-on session where you’ll build it, break it, debug it, and fix it. You’ll set up Sentry, track errors, use Session Replay and Tracing, and leverage some good ol’ AI to find and fix issues fast.

RSVP here →

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more

👋 Kindness is contagious

Please leave a ❤️ or a friendly comment on this post if you found it helpful!

Okay