MeridianMERIDIAN

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.

Worked samples

DuckDB 1.5.5 · finetype 0.6.64

ValueAs TIMESTAMPTZ
15/Jan/2024:14:30:00 +00002024-01-15 14:30:00+00
31/Dec/2023:23:59:59 -05002024-01-01 04:59:59+00
01/Jun/2024:08:15:30 +05302024-06-01 02:45:30+00
%d
day of month, two digits
%b
abbreviated month name
%Y
four-digit year
%H
hour, 24-hour clock
%M
minutes
%S
seconds
%z
UTC offset
Standard

Shape

Length 24 to 29 characters.

Pattern
^\[?\d{2}/[A-Z][a-z]{2}/\d{4}:\d{2}:\d{2}:\d{2} [+-]\d{2}(?::?\d{2})?\]?$
ValueLengthShape
15/Jan/2024:14:30:00 +000026matches
31/Dec/2023:23:59:59 -050026matches
01/Jun/2024:08:15:30 +053026matches
15/Jan/2024:14:30:00 +00025rejected: one character short

Background

Origin

The W3C httpd documentation says that previously every web server had its own logfile format, which made it difficult to write general statistics collectors.[1] W3C httpd generated all of its log files in the common logfile format that several WWW servers use.[1] The W3C httpd documentation lays out a common logfile entry as remotehost rfc931 authuser [date] "request" status bytes.[1] Times in a W3C httpd log file are local time by default.[1] The Common Log Format is also known as the NCSA Common log format.[2]

The Apache HTTP Server documentation defines the CLF time field as [day/month/year:hour:minute:second zone].[3] In the Apache definition, the day is two digits, the month is three letters and the year is four digits.[3] In the Apache definition, the zone is a plus or minus sign followed by four digits.[3] Apache's example CLF timestamp is [10/Oct/2000:13:55:36 -0700].[3] Wikipedia gives the Common Log Format timestamp in strftime notation as %d/%b/%Y:%H:%M:%S %z.[2]

Use

Apache's documentation says the Common Log Format can be produced by many different web servers and read by many log analysis programs.[3] In Apache's log format strings, %t is the time the request was received, and its last number is the timezone offset from GMT.[4] Apache can write the time in another format through %{format}t, using an extended strftime(3) format that is potentially localized.[4] In nginx, the variable $time_local holds local time in the Common Log Format.[5] The nginx predefined combined log format places $time_local inside square brackets.[5] nginx also offers $time_iso8601, which holds local time in the ISO 8601 standard format.[5] Wikipedia notes that the Combined Log Format extends the Common Log Format with referer and user-agent fields.[2]

If a server may have been set up with a custom time format, check a few lines for the month names before trusting a parse. (Meridian's view)

Sources

  1. [1]Logging in W3C httpddocumentation
  2. [2]Common Log Format - Wikipediasecondary source
  3. [3]Log Files - Apache HTTP Server Version 2.4documentation
  4. [4]mod_log_config - Apache HTTP Server Version 2.4documentation
  5. [5]Module ngx_http_log_moduledocumentation

Detection notes

New in format coverage expansion. Critical for web log analysis. Highly distinctive format — colon between date:time is unique. The offset takes every form DuckDB's `%z` parses — `+00`, `+0000`, `+00:00` — because `+00` is what DuckDB's own `strftime` writes for this format string, and the transform reads all three.

Use it

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

SELECT ft_infer('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

Schema

https://meridian.online/schemas/datetime.timestamp.clf · Frictionless type datetime · %d/%b/%Y:%H:%M:%S %z

finetype taxonomy datetime.timestamp.clf -o json-schema

Aliases

apachenginx_log

Type Registry