Timestamp Format Specifiers

Log Parser

Timestamp Format Specifiers

TIMESTAMP values are formatted and parsed using Timestamp Format Specifiers. Timestamp format specifiers are strings that use special characters to describe date and/or time elements in a string representation of a timestamp.


Timestamp format specifiers are used in the following circumstances:

  • When entering a TIMESTAMP constant with the TIMESTAMP keyword. In this case, timestamp format specifiers are used to describe how the string entered should be parsed in order to obtain a TIMESTAMP value, as in the following example:
    TimeGenerated > TIMESTAMP ( '2004-05-28 10:23:15', 'yyyy-MM-dd hh:mm:ss' )
    
  • When converting a TIMESTAMP value to a STRING value using the TO_STRING function. In this case, timestamp format specifiers are used to describe how the TIMESTAMP value should be formatted in order to obtain a STRING value, as in the following example:
    TO_STRING(TimeGenerated, 'yyyy MMM, dd h:m:s')
    
  • When converting a STRING value to a TIMESTAMP value using the TO_TIMESTAMP function. In this case, timestamp format specifiers are used to describe how the STRING value should be parsed in order to obtain a TIMESTAMP value, as in the following example:
    TO_TIMESTAMP(Text, 'MMM ddd yyyy')
    
  • When specifying how an input format should parse TIMESTAMP fields, using the "iTsFormat" parameter. In this case, timestamp format specifiers are used to describe how timestamp values are represented by the selected data source, so that the input format is capable to parse these fields and represent them as values of type TIMESTAMP.
    The following example sets a specific value for the "iTsFormat" parameter of the CSV Input Format:
    C:\>logparser "SELECT MyField FROM file.csv" -i:CSV -iTsFormat:"yyyy-MM-dd"
    
  • When specifying how an output format should format and display TIMESTAMP fields, using the "oTsFormat" parameter. In this case, timestamp format specifiers are used to describe how TIMESTAMP values should be formatted by the output format, as in the following example using the TSV Output Format:
    C:\>logparser "SELECT TimeGenerated INTO file.txt FROM System" -i:EVT -o:TSV -oTsFormat:"yyyy-MM-dd"
    


The following table describes the timestamp format specifiers supported by the Log Parser SQL-Like language:

Specifier Description Example specifier strings Example formats
y
year, last digit
(when parsing, assumed to be relative to year 2000)
y MM dd 4 05 28
yy
year, last 2 digits
(when parsing, assumed to be relative to year 2000)
yy MM dd 04 05 28
yyy
year, last 3 digits
(when parsing, assumed to be relative to year 2000)
yyy MM dd 004 05 28
yyyy
year, 4 digits yyyy MM dd 2004 05 28
M
month, no leading zero yyyy-M-dd 2004-5-28
2004-12-01
MM
month, leading zero yyyy-MM-dd 2004-05-28
2004-12-01
MP
month, leading space yyyy-MP-dd 2004- 5-28
2004-12-01
MX
month, with or without leading zero (when parsing)
month, without leading zero (when formatting)
yyyy-MX-dd 2004-05-28 (when parsing)
2004-5-28
2004-12-01
MMM
month, 3-character abbreviation of name (1) MMM d, yyyy Dec 1, 2004
MMMM
month, full name (1) MMMM d, yyyy December 1, 2004
d
day, no leading zero yyyy-MM-d 2004-12-1
2004-05-28
dd
day, leading zero yyyy-MM-dd 2004-12-01
2004-05-28
dp
day, leading space yyyy-MM-dp 2004-12- 1
2004-05-28
dx
day, with or without leading zero (when parsing)
day, without leading zero (when formatting)
yyyy-MM-dx 2004-12-01 (when parsing)
2004-12-1
2004-05-28
ddd
week day, 3-character abbreviation of name (1) ddd MMMM d, yyyy Wed December 1, 2004
dddd
week day, full name (1) dddd MMMM d, yyyy Wednesday December 1, 2004
h, H
hour, no leading zero h:mm:ss 3:12:05
21:04:15
hh, HH
hour, leading zero hh:mm:ss 03:12:05
21:04:15
hp, HP
hour, leading space hp:mm:ss  3:12:05
21:04:15
hx, HX
hour, with or without leading zero (when parsing)
hour, without leading zero (when formatting)
hx:mm:ss 03:12:05 (when parsing)
3:12:05
21:04:15
m
minute, no leading zero hh:m:ss 21:4:15
03:12:05
mm
minute, leading zero hh:mm:ss 21:04:15
03:12:05
mp
minute, leading space hh:mp:ss 21: 4:15
03:12:05
mx
minute, with or without leading zero (when parsing)
minute, without leading zero (when formatting)
hh:mx:ss 21:04:15 (when parsing)
21:4:15
3:12:05
s
second, no leading zero hh:mm:ss 03:12:5
21:04:15
ss
second, leading zero hh:mm:ss 03:12:05
21:04:15
sp
second, leading space hh:mm:sp 03:12: 5
21:04:15
sx
second, with or without leading zero (when parsing)
second, without leading zero (when formatting)
hh:mm:ss 03:12:05 (when parsing)
03:12:5
21:04:15
l
millisecond, no leading zeroes hh:mm:ss.l 21:4:15.5
03:12:05.395
ll
millisecond, leading zeroes hh:mm:ss.ll 21:04:15.005
03:12:05.395
lp
millisecond, leading spaces hh:mm:ss.lp 21:04:15.  5
03:12:05.395
lx
millisecond, with or without leading zero (when parsing)
millisecond, without leading zero (when formatting)
hh:mm:ss.lx 21:04:15.005 (when parsing)
21:04:15.5
3:12:05.395
n
nanosecond, no leading zeroes hh:mm:ss.ll.n 21:4:15.005.400
03:12:05.395.1900
nn
nanosecond, leading zeroes hh:mm:ss.ll.nn 21:04:15.005.00000400
03:12:05.395.001900
np
nanosecond, leading spaces hh:mm:ss.ll.np 21:04:15.005.     400
03:12:05.395.  1900
nx
nanosecond, with or without leading zero (when parsing)
nanosecond, without leading zero (when formatting)
hh:mm:ss.ll.nx 21:04:15.005.00000400 (when parsing)
21:04:15.005.400
3:12:05.395.1900
tt
AM/PM notation hh:mm:ss tt 09:04:15 PM
03:12.05 AM
?
any character (when parsing)
space (when formatting)
yyyy-MM-dd?hh:mm:ss 2004-05-28T21:04:15 (when parsing)
2004-05-28 21:04:15 (when formatting)
any other
character
verbatim character hh:mm:ss --- yyyy.MM+dd 09:04:15 --- 2004.05+28


Notes:
(1): element names are obtained from the current system locale.


Date-only and Time-only Timestamps

When parsing a timestamp string, the following assumptions are made:
  • If the timestamp format specifiers include date elements only, the resulting TIMESTAMP value will be a date-only timestamp; for example, the following statement creates a date-only TIMESTAMP constant value:
    TIMESTAMP('2004-05-28', 'yyyy-MM-dd')
    
  • If the timestamp format specifiers include time of day elements only, the resulting TIMESTAMP value will be a time-only timestamp; for example, the following statement creates a time-only TIMESTAMP constant value:
    TIMESTAMP('21:04:15', 'hh:mm:ss')
    
  • Unspecified date elements are replaced with the corresponding elements of the Log Parser origin date (January 1, year 0), unless the timestamp is a time-only timestamp value; for example, the following statement creates a date-only timestamp representing the date February 1, year 0:
    TIMESTAMP('2', 'M')
    
    Similarly, unspecified time elements are replaced with zero values, unless the timestamp is a date-only timestamp value; for example, the following statement creates a time-only timestamp representing the time 10:00:00.0.0:
    TIMESTAMP('10', 'h')
    
    As another example, the following statement creates a full timestamp value representing the time 10:00:00.0.0 on February 1, year 0:
    TIMESTAMP('2 10', 'M h')
    
For more information on date-only and time-only timestamp values, refer to the Timestamp Data Type reference.


See also:

Constant Values
Timestamp Data Type


© 2004 Microsoft Corporation. All rights reserved.