postgresql 日期字符串转换 to_date to_char YYYY-MM-DD HH24:MI:SS_锦天_pgsql日期格式转换yyyymmd

网络 02-07 4985

函数

记住年月日时分秒的模式字符串:YYYY-MM-DD HH24:MI:SS

日期 --> 字符串 to_char(date, formatString)

select to_char(now(), 'YYYY-MM-DD HH24, MI:SS')

字符串 --> 日期 to_date('dateString', 'formatString')

select to_date('2021-12-18 22:24:15', 'YYYY-MM-DD HH24:MI:SS')

ref

官网链接 https://www.postgresql.org/docs/12/functions-formatting.html

完整的表格：

PatternDescriptionHHhour of day (01-12)HH12hour of day (01-12)HH24hour of day (00-23)MIminute (00-59)SSsecond (00-59)MSmillisecond (000-999)USmicrosecond (000000-999999)SSSSseconds past midnight (0-86399)AM, am, PM or pmmeridiem indicator (without periods)A.M., a.m., P.M. or p.m.meridiem indicator (with periods)Y,YYYyear (4 or more digits) with commaYYYYyear (4 or more digits)YYYlast 3 digits of yearYYlast 2 digits of yearYlast digit of yearIYYYISO 8601 week-numbering year (4 or more digits)IYYlast 3 digits of ISO 8601 week-numbering yearIYlast 2 digits of ISO 8601 week-numbering yearIlast digit of ISO 8601 week-numbering yearBC, bc, AD or adera indicator (without periods)B.C., b.c., A.D. or a.d.era indicator (with periods)MONTHfull upper case month name (blank-padded to 9 chars)Monthfull capitalized month name (blank-padded to 9 chars)monthfull lower case month name (blank-padded to 9 chars)MONabbreviated upper case month name (3 chars in English, localized lengths vary)Monabbreviated capitalized month name (3 chars in English, localized lengths vary)monabbreviated lower case month name (3 chars in English, localized lengths vary)MMmonth number (01-12)DAYfull upper case day name (blank-padded to 9 chars)Dayfull capitalized day name (blank-padded to 9 chars)dayfull lower case day name (blank-padded to 9 chars)DYabbreviated upper case day name (3 chars in English, localized lengths vary)Dyabbreviated capitalized day name (3 chars in English, localized lengths vary)dyabbreviated lower case day name (3 chars in English, localized lengths vary)DDDday of year (001-366)IDDDday of ISO 8601 week-numbering year (001-371; day 1 of the year is Monday of the first ISO week)DDday of month (01-31)Dday of the week, Sunday (1) to Saturday (7)IDISO 8601 day of the week, Monday (1) to Sunday (7)Wweek of month (1-5) (the first week starts on the first day of the month)WWweek number of year (1-53) (the first week starts on the first day of the year)IWweek number of ISO 8601 week-numbering year (01-53; the first Thursday of the year is in week 1)CCcentury (2 digits) (the twenty-first century starts on 2001-01-01)JJulian Date (integer days since November 24, 4714 BC at local midnight; see Section B.7)QquarterRMmonth in upper case Roman numerals (I-XII; I=January)rmmonth in lower case Roman numerals (i-xii; i=January)TZupper case time-zone abbreviation (only supported in to_char)tzlower case time-zone abbreviation (only supported in to_char)TZHtime-zone hoursTZMtime-zone minutesOFtime-zone offset from UTC (only supported in to_char)