时间处理

发布时间：2020-12-13 17:51:41 所属栏目：百科来源：网络整理

导读：Postgresql 时间处理取得年份，月份，日 select EXTRACT(year from "worktime") from question select EXTRACT(month from "worktime") from question select EXTRACT(day from "worktime") from question 取得限定指定值之前的值 select date_trunc('hour'

Postgresql　时间处理

取得年份，月份，日

select EXTRACT(year from "worktime") from question

select EXTRACT(month from "worktime") from question

select EXTRACT(day from "worktime") from question

取得限定指定值之前的值

select date_trunc('hour',now());

类型转换

Table 9-20. Formatting Functions

Function	Return Type	Description	Example
`to_char(timestamp,text)`	`text`	convert time stamp to string	`to_char(current_timestamp,'HH12:MI:SS')`
`to_char(interval,text)`	`text`	convert interval to string	`to_char(interval '15h2m12s','HH24:MI:SS')`
`to_char(int,text)`	`text`	convert integer to string	`to_char(125,'999')`
`to_char(double precision,text)`	`text`	convert real/double precision to string	`to_char(125.8::real,'999D9')`
`to_char(numeric,text)`	`text`	convert numeric to string	`to_char(-125.8,'999D99S')`
`to_date(text,text)`	`date`	convert string to date	`to_date('05Dec2000','DDMonYYYY')`
`to_number(text,text)`	`numeric`	convert string to numeric	`to_number('12,454.8-','99G999D9S')`
`to_timestamp(text,text)`	`timestamp with time zone`	convert string to time stamp	`to_timestamp('05Dec2000','DDMonYYYY')`
`to_timestamp(double precision)`	`timestamp with time zone`	convert Unix epoch to time stamp	`to_timestamp(1284352323)`

以上转换注意括号里面的类型。类型不对，转换就会出错，它本身不会自己进行隐式转换

Pattern	Description
`HH`	hour of day (01-12)
`HH12`	hour of day (01-12)
`HH24`	hour of day (00-23)
`MI`	minute (00-59)
`SS`	second (00-59)
`MS`	millisecond (000-999)
`US`	microsecond (000000-999999)
`SSSS`	seconds past midnight (0-86399)
`AM`,`am`,`PM` or `pm`	meridiem indicator (without periods)
`A.M.`,`a.m.`,`P.M.` or `p.m.`	meridiem indicator (with periods)
`Y,YYY`	year (4 and more digits) with comma
`YYYY`	year (4 and more digits)
`YYY`	last 3 digits of year
`YY`	last 2 digits of year
`Y`	last digit of year
`IYYY`	ISO year (4 and more digits)
`IYY`	last 3 digits of ISO year
`IY`	last 2 digits of ISO year
`I`	last digit of ISO year
`BC`,`bc`,`AD` or `ad`	era indicator (without periods)
`B.C.`,`b.c.`,`A.D.` or `a.d.`	era indicator (with periods)
`MONTH`	full upper case month name (blank-padded to 9 chars)
`Month`	full capitalized month name (blank-padded to 9 chars)
`month`	full lower case month name (blank-padded to 9 chars)
`MON`	abbreviated upper case month name (3 chars in English,localized lengths vary)
`Mon`	abbreviated capitalized month name (3 chars in English,localized lengths vary)
`mon`	abbreviated lower case month name (3 chars in English,localized lengths vary)
`MM`	month number (01-12)
`DAY`	full upper case day name (blank-padded to 9 chars)
`Day`	full capitalized day name (blank-padded to 9 chars)
`day`	full lower case day name (blank-padded to 9 chars)
`DY`	abbreviated upper case day name (3 chars in English,localized lengths vary)
`Dy`	abbreviated capitalized day name (3 chars in English,localized lengths vary)
`dy`	abbreviated lower case day name (3 chars in English,localized lengths vary)
`DDD`	day of year (001-366)
`IDDD`	ISO day of year (001-371; day 1 of the year is Monday of the first ISO week.)
`DD`	day of month (01-31)
`D`	day of the week,Sunday(`1`) to Saturday(`7`)
`ID`	ISO day of the week,Monday(`1`) to Sunday(`7`)
`W`	week of month (1-5) (The first week starts on the first day of the month.)
`WW`	week number of year (1-53) (The first week starts on the first day of the year.)
`IW`	ISO week number of year (01 - 53; the first Thursday of the new year is in week 1.)
`CC`	century (2 digits) (The twenty-first century starts on 2001-01-01.)
`J`	Julian Day (days since November 24,4714 BC at midnight)
`Q`	quarter (ignored by `to_date` and `to_timestamp`)
`RM`	month in upper case Roman numerals (I-XII; I=January)
`rm`	month in lower case Roman numerals (i-xii; i=January)
`TZ`	upper case time-zone name
`tz`	lower case time-zone name

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!