ClickHouse DateTIme DateTime64 字符串时间戳转化

由木头丶发布于 2022-05-26 01:00:25

yearweek		toYearWeek
yesterday		
today		
week		toWeek
now64		

fromUnixTimestamp64Nano		
fromUnixTimestamp64Micro		
fromUnixTimestamp64Milli		
fromUnixTimestamp		FROM_UNIXTIME

toUnixTimestamp64Nano		
toUnixTimestamp64Micro		
toUnixTimestamp64Milli		
toUnixTimestamp		

subtractYears		
subtractQuarters		
subtractHours		
subtractSeconds		
subtractDays		

toStartOfDay			
toStartOfFiveMinute		
toStartOfISOYear		
toStartOfFifteenMinutes	
toStartOfMinute		
toStartOfYear		
toStartOfMonth		
toStartOfWeek		
toStartOfQuarter		
toStartOfHour		
toStartOfSecond		

toDateTime64OrNull		
toDateTimeOrZero		
toDateTimeOrNull		

addMinutes			
addMonths		
addQuarters		
addWeeks		
addHours		
addSeconds		
addDays		

toYYYYMMDDhhmmss		
toYYYYMM		
toYYYYMMDD		

FROM_UNIXTIME			

parseDateTime64BestEffortOrNull		
parseDateTimeBestEffortOrNull		
parseDateTimeBestEffortOrZero		
parseDateTimeBestEffort		
parseDateTimeBestEffortUS	
parseDateTime64BestEffort	

toRelativeMinuteNum		
toRelativeYearNum		
toDecimal32OrNull		
toWeek		
toMonday		
toMinute		
toDayOfYear		
toISOWeek		
toMonth		
toQuarter		
toIntervalYear		
toIntervalQuarter		
toSecond		
toIntervalMonth		
toDayOfWeek		
toIntervalWeek		
toIntervalHour		
toIntervalSecond		
toRelativeWeekNum		
toDateOrNull		
toRelativeDayNum		
toHour		
toYearWeek		
toDateOrZero		
toDayOfMonth		
toYear		
toISOYear

SELECT 
    now() AS dt,
    toYYYYMMDDhhmmss(dt) AS dt_int,
    toString(dt) AS dt_str,
    parseDateTimeBestEffort(toString(dt_int)) AS datetime
 
┌──────────────────dt─┬─────────dt_int─┬─dt_str──────────────┬────────────datetime─┐
│ 2020-07-28 10:01:42 │ 20200728100142 │ 2020-07-28 10:01:42 │ 2020-07-28 10:01:42 │
└─────────────────────┴────────────────┴─────────────────────┴─────────────────────┘


SELECT 
    now() AS dt,
    now64(3) AS ms,
    now64(6) AS us,
    now64(9) AS ns
 
┌──────────────────dt─┬──────────────────────ms─┬─────────────────────────us─┬────────────────────────────ns─┐
│ 2020-07-28 10:33:19 │ 2020-07-28 10:33:19.070 │ 2020-07-28 10:33:19.070586 │ 2020-07-28 10:33:19.070596096 │
└─────────────────────┴─────────────────────────┴────────────────────────────┴───────────────────────────────┘
 
--------------------------------------------------------
SELECT *
FROM system.functions
WHERE lower(name) LIKE 'fromunix%'
UNION ALL
SELECT *
FROM system.functions
WHERE lower(name) LIKE 'tounix%'
FORMAT PrettyCompactMonoBlock
 
┌─name─────────────────────┬─is_aggregate─┬─case_insensitive─┬─alias_to─┐
│ fromUnixTimestamp64Nano  │            0 │                0 │          │
│ fromUnixTimestamp64Micro │            0 │                0 │          │
│ fromUnixTimestamp64Milli │            0 │                0 │          │
│ toUnixTimestamp64Nano    │            0 │                0 │          │
│ toUnixTimestamp64Micro   │            0 │                0 │          │
│ toUnixTimestamp64Milli   │            0 │                0 │          │
│ toUnixTimestamp          │            0 │                0 │          │
└──────────────────────────┴──────────────┴──────────────────┴──────────┘

--------------------------------------------------------
SELECT 
    now() AS dt,
    toYYYYMMDDhhmmss(dt) AS dt_int,
    toString(dt) AS dt_str,
    parseDateTimeBestEffort(toString(dt_int)) AS datetime
 
┌──────────────────dt─┬─────────dt_int─┬─dt_str──────────────┬────────────datetime─┐
│ 2020-07-28 10:32:52 │ 20200728103252 │ 2020-07-28 10:32:52 │ 2020-07-28 10:32:52 │
└─────────────────────┴────────────────┴─────────────────────┴─────────────────────┘
 
1 rows in set. Elapsed: 0.005 sec. 
 
Clickhouse> select now64() dt,toUnixTimestamp(dt) ut,toDateTime(ut) datetime;
 
SELECT 
    now64() AS dt,
    toUnixTimestamp(dt) AS ut,
    toDateTime(ut) AS datetime
 
┌──────────────────────dt─┬─────────ut─┬────────────datetime─┐
│ 2020-07-28 10:34:25.161 │ 1595903665 │ 2020-07-28 10:34:25 │
└─────────────────────────┴────────────┴─────────────────────┘
 
 
--------------------------------------------------------
SELECT 
    now64(3) AS dt,
    toUnixTimestamp64Milli(dt) AS ut,
    fromUnixTimestamp64Milli(ut) AS datetime
 
┌──────────────────────dt─┬────────────ut─┬────────────────datetime─┐
│ 2020-07-28 10:34:47.731 │ 1595903687731 │ 2020-07-28 10:34:47.731 │
└─────────────────────────┴───────────────┴─────────────────────────┘
 
-------------------------------------------------------- 
SELECT 
    now64(6) AS dt,
    toUnixTimestamp64Micro(dt) AS ut,
    fromUnixTimestamp64Micro(ut) AS datetime
 
┌─────────────────────────dt─┬───────────────ut─┬───────────────────datetime─┐
│ 2020-07-28 10:35:23.209645 │ 1595903723209645 │ 2020-07-28 10:35:23.209645 │
└────────────────────────────┴──────────────────┴────────────────────────────┘

-------------------------------------------------------- 
SELECT 
    now64(9) AS dt,
    toUnixTimestamp64Nano(dt) AS ut,
    fromUnixTimestamp64Nano(ut) AS datetime
 
┌────────────────────────────dt─┬──────────────────ut─┬──────────────────────datetime─┐
│ 2020-07-28 10:35:49.248605839 │ 1595903749248605839 │ 2020-07-28 10:35:49.248605839 │
└───────────────────────────────┴─────────────────────┴───────────────────────────────┘

--------------------------------------------------------
SELECT 
    now64(9) AS dt,
    toUnixTimestamp64Nano(dt) AS ut,
    fromUnixTimestamp64Nano(ut) AS datetime_ns,
    fromUnixTimestamp64Micro(ut) AS us,
    fromUnixTimestamp64Milli(ut) AS ms
 
┌────────────────────────────dt─┬──────────────────ut─┬───────────────────datetime_ns─┬─────────────────────────us─┬──────────────────────ms─┐
│ 2020-07-28 10:37:10.983299230 │ 1595903830983299230 │ 2020-07-28 10:37:10.983299230 │ 2106-02-07 51:43:03.299230 │ 2106-01-27 05:34:59.230 │
└───────────────────────────────┴─────────────────────┴───────────────────────────────┴────────────────────────────┴─────────────────────────┘

本作品采用《署名-非商业性使用-禁止演绎 4.0 国际》许可协议，转载必须注明作者和本文链接

请登录后发表观点

ClickHouse DateTIme DateTime64 字符串 时间戳 转化

ClickHouse DateTIme DateTime64 字符串时间戳转化