Parse logs and query by time

Implement a parser that converts raw log lines into structured records with fields {timestamp, level, message}. Build an API to support: (a) insert(logLine), (b) query(startTime, endTime, levelFilter?) returning records in time order. Explain how you will parse timestamps and time zones, handle malformed lines, and support large-scale data (e.g., indexing by time, batching, or using SQL windowing). Analyze time and space complexity and provide basic tests.

Parse raw log lines into structured fields

You are given application logs stored as raw text lines in raw_logs. Each well-formed line has this format:

YYYY-MM-DD HH:MI:SS LEVEL message...

The timestamp is UTC and occupies the first 19 characters. LEVEL is a single uppercase word, followed by a space and the message text. Some rows are malformed and should be ignored.

Write a PostgreSQL query that returns one row per well-formed log line with these columns:

• log_ts: the parsed timestamp rendered as YYYY-MM-DD HH24:MI:SS
• level: the log level immediately after the timestamp
• message: everything after the level and following space

Filter out malformed rows, parse the timestamp from the first 19 characters, and order the result by the parsed timestamp ascending.

Table raw_logs

column	type	constraints
id	INT	PRIMARY KEY, NOT NULL
raw_line	VARCHAR(255)	NOT NULL

Sample data:

id	raw_line
1	2025-05-31 23:59:59 INFO Server started
2	2025-06-01 00:00:01 WARN Cache miss for key 123
3	2025-06-01 01:15:30 ERROR Failed to connect to DB
4	MALFORMED LINE WITHOUT TIMESTAMP
5	2025-06-01 02:00:00 INFO User login: user_id=42

Expected output

The query should return one row per well-formed log line, with parsed timestamp, level, and message, ordered by log_ts ascending. The malformed line (id = 4) must be excluded.

level	log_ts	message
INFO	2025-05-31 23:59:59	Server started
WARN	2025-06-01 00:00:01	Cache miss for key 123
ERROR	2025-06-01 01:15:30	Failed to connect to DB
INFO	2025-06-01 02:00:00	User login: user_id=42

Hints

1. Use a regular expression to discard rows that do not match the log-line shape before casting the timestamp.
2. Parse the first 19 characters as a timestamp, then split the text that begins at character 21 into level and message.
3. Use PostgreSQL TO_CHAR(..., 'YYYY-MM-DD HH24:MI:SS') when the expected output shows formatted timestamp strings.

Query logs by time range and level

Assume you already have a structured logs table with one row per log entry, including parsed timestamp, level, and message.

For this query, use these concrete API parameters:

• startTime = '2025-06-01 00:00:00'
• endTime = '2025-06-01 23:59:59'
• levelFilter = 'INFO'

Write a PostgreSQL query that returns all records where log_ts is between startTime and endTime inclusive and level equals levelFilter. Return id, log_ts, level, and message, with log_ts rendered as YYYY-MM-DD HH24:MI:SS. Order the result by log_ts ascending, using id as a tiebreaker.

Table logs

column	type	constraints
id	INT	PRIMARY KEY, NOT NULL
log_ts	TIMESTAMP	NOT NULL
level	VARCHAR(10)	NOT NULL
message	VARCHAR(255)	NOT NULL

Sample data:

id	level	log_ts	message
1	INFO	2025-05-31 23:59:59	Server started
2	WARN	2025-06-01 00:00:01	Cache miss for key 123
3	ERROR	2025-06-01 01:15:30	Failed to connect to DB
4	INFO	2025-06-01 02:00:00	User login: user_id=42
5	DEBUG	2025-06-01 03:00:00	Background job started
6	INFO	2025-06-02 10:00:00	Daily summary

Expected output

For the given parameters (startTime = '2025-06-01 00:00:00', endTime = '2025-06-01 23:59:59', levelFilter = 'INFO'), only INFO logs on 2025-06-01 should be returned, ordered by log_ts ascending.

id	level	log_ts	message
4	INFO	2025-06-01 02:00:00	User login: user_id=42

Hints

1. Use BETWEEN on log_ts for the inclusive time window and an AND predicate for the level filter.
2. Use TO_CHAR(log_ts, 'YYYY-MM-DD HH24:MI:SS') to match the timestamp string shown in the expected result.
3. Order by log_ts and then id for deterministic output.