Common Log Format (Apache/Nginx)

datetime.timestamp.clf

Apache and Nginx Common Log Format timestamp. Note the distinctive colon between date and time with no space, and abbreviated month. Billions of log lines generated daily in this format worldwide.

Domain

datetime

Try it

CLI

$ finetype infer -i "15/Jan/2024:14:30:00 +0000"
→ datetime.timestamp.clf

DuckDB

Detect

SELECT finetype('15/Jan/2024:14:30:00 +0000');
-- → 'datetime.timestamp.clf'

Cast expression

strptime({col}, '%d/%b/%Y:%H:%M:%S %z')
-- Format: %d/%b/%Y:%H:%M:%S %z

Safe cast pipeline

-- Normalise and cast in one step
SELECT TRY_CAST(finetype_cast(my_column) AS TIMESTAMPTZ) AS clean_value
FROM my_table
WHERE finetype(my_column) = 'datetime.timestamp.clf';

JSON Schema

finetype schema datetime.timestamp.clf

{
  "$id": "https://meridian.online/schemas/datetime.timestamp.clf",
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "description": "Apache and Nginx Common Log Format timestamp. Note the distinctive colon between date and time with no space, and abbreviated month. Billions of log lines generated daily in this format worldwide.",
  "examples": [
    "15/Jan/2024: 14: 30: 00 +0000",
    "31/Dec/2023: 23: 59: 59 -0500",
    "01/Jun/2024: 08: 15: 30 +0530"
  ],
  "maxLength": 26,
  "minLength": 26,
  "pattern": "^\\d{2}/[A-Z][a-z]{2}/\\d{4}:\\d{2}:\\d{2}:\\d{2} [+-]\\d{4}$",
  "title": "Common Log Format (Apache/Nginx)",
  "type": "string",
  "x-finetype-broad-type": "VARCHAR",
  "x-finetype-format-string": "%d/%b/%Y:%H:%M:%S %z",
  "x-finetype-transform": "strptime({col}, '%d/%b/%Y:%H:%M:%S %z')"
}

Examples

15/Jan/2024:14:30:00 +000031/Dec/2023:23:59:59 -050001/Jun/2024:08:15:30 +0530

Aliases

apachenginx_log