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 |
