英文:
Kusto convert string to date
问题
使用Kusto查询时，我有一个日期时间格式为yyyyMMdd HHmmss.SSS，例如：20230726 121033.000。当使用todatetime函数时，我无法将格式转换为日期/时间。
project todatetime(['TimeStamp_s'])不返回任何结果。
英文:
Writing a kusto query I have a date time format of yyyyMMdd HHmmss.SSS eg: 20230726 121033.000. When using the todatetime I am unable to convert the format to date/time.
project todatetime(['TimeStamp_s']) does not return any result.
答案1
得分: 0
"yyyyMMdd HHmmss.SSS" 不支持的格式。请参考此文档以获取Kusto datetime()函数支持的格式。
let MyTable = datatable(TimeStamp_s:string, OtherColumn1:string, OtherColumn2:string)
[
'20230726 121033.000', 'Value1', 'Value2',
'20230727 093015.123', 'Value3', 'Value4',
'20230728 184500.999', 'Value5', 'Value6'
];
MyTable
| project todatetime(strcat(substring(['TimeStamp_s'],0,4),'-',substring(['TimeStamp_s'],4,2),'-',substring(['TimeStamp_s'],6,2),'T',substring(['TimeStamp_s'],9,2),':',substring(['TimeStamp_s'],11,2),':',substring(['TimeStamp_s'],13,6)))
substring()函数用于从TimeStamp_s字符串中提取年、月、日、小时、分钟和秒的组件，而strcat()函数用于将它们连接成一个字符串。这样，格式就更改为ISO 8601日期时间格式，并作为输入传递给todatetime()函数。
英文:
yyyyMMdd HHmmss.SSS is not supported format. Refer this document for the supported formats in Kusto datetime() function.
let MyTable = datatable(TimeStamp_s:string, OtherColumn1:string, OtherColumn2:string)
[
'20230726 121033.000', 'Value1', 'Value2',
'20230727 093015.123', 'Value3', 'Value4',
'20230728 184500.999', 'Value5', 'Value6'
];
MyTable
| project todatetime(strcat(substring(['TimeStamp_s'],0,4),'-',substring(['TimeStamp_s'],4,2),'-',substring(['TimeStamp_s'],6,2),'T',substring(['TimeStamp_s'],9,2),':',substring(['TimeStamp_s'],11,2),':',substring(['TimeStamp_s'],13,6)))
The substring() function is used to extract the year, month, day, hour, minute, and second components from the TimeStamp_s string, and the strcat() function is used to concatenate them into a string. This way, format is changed to ISO 8601 datetime format and given as input to todatetime() function.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论