MeridianMERIDIAN

Slash YMD Timestamp (24h)

datetime.timestamp.slash_ymd_24h

Slash-separated Year/Month/Day with space and 24-hour time. Standard in Japanese system logs and .NET ja-JP culture default.

Operations on this type: Parse a date or time

Try a value

Slash YMD Timestamp (24h)'s rule accepts it.

Reads it as 2024-03-04 05:06:07

Use it

Shell
finetype infer -i '2024/01/15 14:30:00' --mode column
# → datetime.timestamp.slash_ymd_24h
DuckDB
SELECT ft_infer('2024/01/15 14:30:00');
-- → 'datetime.timestamp.slash_ymd_24h'
Cast template: {col} is your column
strptime({col}, '%Y/%m/%d %H:%M:%S')
-- Format: %Y/%m/%d %H:%M:%S

How finetype reads the samples

DuckDB 1.5.5 · finetype 0.6.66

ValueAs TIMESTAMP
2024/01/15 14:30:002024-01-15 14:30:00
2023/12/31 23:59:592023-12-31 23:59:59
2024/06/01 08:15:302024-06-01 08:15:30
%Y
four-digit year
%m
month, two digits
%d
day of month, two digits
%H
hour, 24-hour clock
%M
minutes
%S
seconds

Pattern

Regular expression
^\d{4}/\d{2}/\d{2} \d{2}:\d{2}:\d{2}$
ValueLengthShape
2024/01/15 14:30:0019matches
2023/12/31 23:59:5919matches
2024/06/01 08:15:3019matches
2024/01/15 14:30:018rejected: one character short

Background

Origin

In Japan, the most commonly used date format puts the year first, then the month and the day, followed by the weekday.[1] Wikipedia's article on date and time notation in Japan shows the all-numeric Gregorian date written with slashes between year, month and day.[1] The 24-hour notation is commonly used in Japan, especially in train schedules.[1] Wikipedia's article also notes that the 12-hour notation is common in Japan.[1]

Microsoft's .NET documentation gives yyyy/MM/dd as the short date pattern of the ja-JP culture.[2][3] In .NET's ja-JP culture, the short date format renders 15 June 2009 as 2009/06/15.[3]

Use

MySQL permits a relaxed DATETIME string syntax in which any punctuation character may be used as the delimiter between date parts or time parts.[4] MySQL's manual gives '2012/12/31 11*30*45' as equivalent to '2012-12-31 11:30:45'.[4] MySQL raises a warning when a datetime value uses delimiters other than the dash for the date part and the colon for the time part.[4] Loading slash-separated timestamps into MySQL by relying on the relaxed syntax is a habit worth dropping; convert them to the dashed form on the way in. (Meridian's view)

Python's strptime treats the leading zero as optional for the %d, %m, %H, %M and %S directives.[5] DuckDB's strptime function converts strings to timestamps according to a specified pattern.[6] DuckDB's strptime function throws an error on failure.[6] DuckDB's try_strptime function returns NULL on failure.[6] Parse with the explicit pattern %Y/%m/%d %H:%M:%S rather than a locale default, and run the forgiving variant first so you can count the rows that do not fit before you commit to the strict one. (Meridian's view) The values carry no offset, so ask the source which time zone its clock was set to before you compare them with data from anywhere else. (Meridian's view)

Sources

  1. [1]Date and time notation in Japansecondary source
  2. [2]DateTimeFormatInfo.ShortDatePattern Property (System.Globalization)documentation
  3. [3]Standard date and time format strings - .NETdocumentation
  4. [4]11.1.3 Date and Time Literalsdocumentation
  5. [5]datetime — Basic date and time typesdocumentation
  6. [6]Date Format Functionsdocumentation

How it's detected

Japanese system logs, .NET ja-JP culture.

Schema

https://meridian.online/schemas/datetime.timestamp.slash_ymd_24h · Frictionless type datetime · %Y/%m/%d %H:%M:%S

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

Aliases

jp_timestamp

Type Registry