greenplum 日期格式化函数

Data Type Formatting Functions

The PostgreSQL formatting functions provide a powerful set of tools for converting various data types (date/time, integer, floating point, numeric) to formatted strings and for converting from formatted strings to specific data types. Table 9-20 lists them. These functions all follow a common calling convention: the first argument is the value to be formatted and the second argument is a template that defines the output or input format.

The to_timestamp function can also take a single double precision argument to convert from Unix epoch to timestamp with time zone. (Integer Unix epochs are implicitly cast to double precision.)

Table 9-20. Formatting Functions

FunctionReturn TypeDescriptionExample
to_char(timestamp, text)textconvert time stamp to stringto_char(current_timestamp, 'HH12:MI:SS')
to_char(interval, text)textconvert interval to stringto_char(interval '15h 2m 12s', 'HH24:MI:SS')
to_char(int, text)textconvert integer to stringto_char(125, '999')
to_char(double precision, text)textconvert real/double precision to stringto_char(125.8::real, '999D9')
to_char(numeric, text)textconvert numeric to stringto_char(-125.8, '999D99S')
to_date(text, text)dateconvert string to dateto_date('05 Dec 2000', 'DD Mon YYYY')
to_number(text, text)numericconvert string to numericto_number('12,454.8-', '99G999D9S')
to_timestamp(text, text)timestamp with time zoneconvert string to time stampto_timestamp('05 Dec 2000', 'DD Mon YYYY')
to_timestamp(double precision)timestamp with time zoneconvert UNIX epoch to time stampto_timestamp(200120400)

In an output template string (for to_char), there are certain patterns that are recognized and replaced with appropriately-formatted data from the value to be formatted. Any text that is not a template pattern is simply copied verbatim. Similarly, in an input template string (for anything but to_char), template patterns identify the parts of the input data string to be looked at and the values to be found there.

Table 9-21 shows the template patterns available for formatting date and time values.

Table 9-21. Template Patterns for Date/Time Formatting

PatternDescription
HHhour 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 or A.M. or PM or P.M.meridian indicator (uppercase)
am or a.m. or pm or p.m.meridian indicator (lowercase)
Y,YYYyear (4 and more digits) with comma
YYYYyear (4 and more digits)
YYYlast 3 digits of year
YYlast 2 digits of year
Ylast digit of year
IYYYISO year (4 and more digits)
IYYlast 3 digits of ISO year
IYlast 2 digits of ISO year
Ilast digits of ISO year
BC or B.C. or AD or A.D.era indicator (uppercase)
bc or b.c. or ad or a.d.era indicator (lowercase)
MONTHfull uppercase month name (blank-padded to 9 chars)
Monthfull mixed-case month name (blank-padded to 9 chars)
monthfull lowercase month name (blank-padded to 9 chars)
MONabbreviated uppercase month name (3 chars in English, localized lengths vary)
Monabbreviated mixed-case month name (3 chars in English, localized lengths vary)
monabbreviated lowercase month name (3 chars in English, localized lengths vary)
MMmonth number (01-12)
DAYfull uppercase day name (blank-padded to 9 chars)
Dayfull mixed-case day name (blank-padded to 9 chars)
dayfull lowercase day name (blank-padded to 9 chars)
DYabbreviated uppercase day name (3 chars in English, localized lengths vary)
Dyabbreviated mixed-case day name (3 chars in English, localized lengths vary)
dyabbreviated lowercase day name (3 chars in English, localized lengths vary)
DDDday of year (001-366)
DDday of month (01-31)
Dday of week (1-7; Sunday is 1)
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.)
IWISO week number of year (The first Thursday of the new year is in week 1.)
CCcentury (2 digits) (The twenty-first century starts on 2001-01-01.)
JJulian Day (days since January 1, 4712 BC)
Qquarter
RMmonth in Roman numerals (I-XII; I=January) (uppercase)
rmmonth in Roman numerals (i-xii; i=January) (lowercase)
TZtime-zone name (uppercase)
tztime-zone name (lowercase)

Certain modifiers may be applied to any template pattern to alter its behavior. For example, FMMonth is the Month pattern with the FM modifier. Table 9-22 shows the modifier patterns for date/time formatting.

Table 9-22. Template Pattern Modifiers for Date/Time Formatting

ModifierDescriptionExample
FM prefixfill mode (suppress padding blanks and zeroes)FMMonth
TH suffixuppercase ordinal number suffixDDTH
th suffixlowercase ordinal number suffixDDth
FX prefixfixed format global option (see usage notes)FX Month DD Day
TM prefixtranslation mode (print localized day and month names based on lc_messages)TMMonth
SP suffixspell mode (not yet implemented)DDSP

Usage notes for date/time formatting:

  • FM suppresses leading zeroes and trailing blanks that would otherwise be added to make the output of a pattern be fixed-width.

  • TM does not include trailing blanks.

  • to_timestamp and to_date skip multiple blank spaces in the input string if the FX option is not used. FX must be specified as the first item in the template. For example to_timestamp('2000    JUN', 'YYYY MON') is correct, but to_timestamp('2000    JUN', 'FXYYYY MON') returns an error, because to_timestamp expects one space only.

  • Ordinary text is allowed in to_char templates and will be output literally. You can put a substring in double quotes to force it to be interpreted as literal text even if it contains pattern key words. For example, in '"Hello Year "YYYY', the YYYY will be replaced by the year data, but the single Y in Year will not be.

  • If you want to have a double quote in the output you must precede it with a backslash, for example E'\\"YYYY Month\\"'. (Two backslashes are necessary because the backslash already has a special meaning when using the escape string syntax.)

  • The YYYY conversion from string to timestamp or date has a restriction if you use a year with more than 4 digits. You must use some non-digit character or template after YYYY, otherwise the year is always interpreted as 4 digits. For example (with the year 20000): to_date('200001131', 'YYYYMMDD') will be interpreted as a 4-digit year; instead use a non-digit separator after the year, like to_date('20000-1131', 'YYYY-MMDD') or to_date('20000Nov31', 'YYYYMonDD').

  • In conversions from string to timestamp or date, the CC field is ignored if there is a YYY, YYYY or Y,YYY field. If CC is used with YY or Y then the year is computed as (CC-1)*100+YY.

  • Millisecond (MS) and microsecond (US) values in a conversion from string to timestamp are used as part of the seconds after the decimal point. For example to_timestamp('12:3', 'SS:MS') is not 3 milliseconds, but 300, because the conversion counts it as 12 + 0.3 seconds. This means for the format SS:MS, the input values 12:3, 12:30, and 12:300 specify the same number of milliseconds. To get three milliseconds, one must use 12:003, which the conversion counts as 12 + 0.003 = 12.003 seconds.

    Here is a more complex example: to_timestamp('15:12:02.020.001230', 'HH:MI:SS.MS.US') is 15 hours, 12 minutes, and 2 seconds + 20 milliseconds + 1230 microseconds = 2.021230 seconds.

  • to_char's day of the week numbering (see the 'D' formatting pattern) is different from that of the extract function.

  • to_char(interval) formats HH and HH12 as hours in a single day, while HH24 can output hours exceeding a single day, e.g. >24.

Table 9-23 shows the template patterns available for formatting numeric values.

Table 9-23. Template Patterns for Numeric Formatting

PatternDescription
9value with the specified number of digits
0value with leading zeros
. (period)decimal point
, (comma)group (thousand) separator
PRnegative value in angle brackets
Ssign anchored to number (uses locale)
Lcurrency symbol (uses locale)
Ddecimal point (uses locale)
Ggroup separator (uses locale)
MIminus sign in specified position (if number < 0)
PLplus sign in specified position (if number > 0)
SGplus/minus sign in specified position
RNroman numeral (input between 1 and 3999)
TH or thordinal number suffix
Vshift specified number of digits (see notes)
EEEEscientific notation (not implemented yet)

Usage notes for numeric formatting:

  • A sign formatted using SG, PL, or MI is not anchored to the number; for example, to_char(-12, 'S9999') produces '  -12', but to_char(-12, 'MI9999') produces '-  12'. The Oracle implementation does not allow the use of MI ahead of 9, but rather requires that 9 precede MI.

  • 9 results in a value with the same number of digits as there are 9s. If a digit is not available it outputs a space.

  • TH does not convert values less than zero and does not convert fractional numbers.

  • PL, SG, and TH are PostgreSQL extensions.

  • V effectively multiplies the input values by 10^n, where n is the number of digits following V. to_char does not support the use of V combined with a decimal point. (E.g., 99.9V99 is not allowed.)

Table 9-24 shows some examples of the use of the to_char function.

Table 9-24. to_char Examples

ExpressionResult
to_char(current_timestamp, 'Day, DD  HH12:MI:SS')'Tuesday  , 06  05:39:18'
to_char(current_timestamp, 'FMDay, FMDD  HH12:MI:SS')'Tuesday, 6  05:39:18'
to_char(-0.1, '99.99')'  -.10'
to_char(-0.1, 'FM9.99')'-.1'
to_char(0.1, '0.9')' 0.1'
to_char(12, '9990999.9')'    0012.0'
to_char(12, 'FM9990999.9')'0012.'
to_char(485, '999')' 485'
to_char(-485, '999')'-485'
to_char(485, '9 9 9')' 4 8 5'
to_char(1485, '9,999')' 1,485'
to_char(1485, '9G999')' 1 485'
to_char(148.5, '999.999')' 148.500'
to_char(148.5, 'FM999.999')'148.5'
to_char(148.5, 'FM999.990')'148.500'
to_char(148.5, '999D999')' 148,500'
to_char(3148.5, '9G999D999')' 3 148,500'
to_char(-485, '999S')'485-'
to_char(-485, '999MI')'485-'
to_char(485, '999MI')'485 '
to_char(485, 'FM999MI')'485'
to_char(485, 'PL999')'+485'
to_char(485, 'SG999')'+485'
to_char(-485, 'SG999')'-485'
to_char(-485, '9SG99')'4-85'
to_char(-485, '999PR')'<485>'
to_char(485, 'L999')'DM 485
to_char(485, 'RN')'        CDLXXXV'
to_char(485, 'FMRN')'CDLXXXV'
to_char(5.2, 'FMRN')'V'
to_char(482, '999th')' 482nd'
to_char(485, '"Good number:"999')'Good number: 485'
to_char(485.8, '"Pre:"999" Post:" .999')'Pre: 485 Post: .800'
to_char(12, '99V999')' 12000'
to_char(12.4, '99V999')' 12400'
to_char(12.45, '99V9')' 125'

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25223017/viewspace-757071/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/25223017/viewspace-757071/

<think>好的,我现在需要帮助用户在Greenplum SQL中获取日期对应的星期几。根据用户提供的引用,特别是引用[2],里面提到了Greenplum和MySQL在获取周几函数上的差异,可能需要特定的函数来处理。 首先,用户的问题是如何在Greenplum中通过SQL获取日期对应的星期几。我需要回忆一下Greenplum日期函数。通常,在PostgreSQL中,可以使用EXTRACT函数结合dow参数来获取星期几,返回0(星期日)到6(星期六)。Greenplum基于PostgreSQL,所以可能也支持这个函数。 根据引用[2],可能需要调整结果使其符合用户的需求,比如将数字转换为周几的名称。例如,使用CASE语句将0到6转换为星期日到星期六。此外,用户提到的引用[2]还提到需要保持与MySQL函数结果一致,而MySQL的DAYOFWEEK函数返回1(星期日)到7(星期六)。如果用户需要与MySQL兼容,可能需要调整数字,比如在Greenplum的结果上加1,并取模7,或者通过CASE重新映射。 因此,可能的解决方案是使用EXTRACT(DOW FROM date_column)来获取数字,然后通过转换得到星期几的名称。同时,如果需要与MySQL的结果一致,可能需要调整数字的起始值。需要确认Greenplum的EXTRACT(DOW)是否返回0-6,如果是的话,加1就可以对齐到1-7,对应星期日到星期六。 另外,用户可能还需要将数字转换为中文的星期名称,比如“星期一”等。这时候可能需要使用CASE语句或者查找表的方式。不过Greenplum可能不支持直接的中文输出,所以可能需要手动映射。 总结步骤: 1. 使用EXTRACT(DOW FROM date_column)获取0-6的数字。 2. 根据需要调整数字,比如加1得到1-7。 3. 使用CASE语句将数字转换为对应的星期名称,例如0→星期日,1→星期一,依此类推。 4. 如果需要与MySQL的结果一致,可能需要调整起始值。 现在需要验证这些步骤是否正确。根据引用[2]中的信息,Greenplum和MySQL的函数不同,所以必须找到正确的函数。例如,Greenplum可能确实使用EXTRACT(DOW),而MySQL使用DAYOFWEEK()。因此,在Greenplum中使用EXTRACT(DOW FROM date)会得到0-6,而MySQL的DAYOFWEEK()返回1-7。如果用户希望两者的结果一致,可能需要在Greenplum中对结果进行调整,比如加1,或者使用不同的映射方式。 另外,用户可能需要将结果转换为星期几的名称,如“Monday”或中文的“星期一”。这时候,可能需要使用CASE WHEN语句将数字转换为对应的名称。例如: CASE WHEN EXTRACT(DOW FROM date_column) = 0 THEN '星期日' ... END 或者,使用to_char函数,但需要确认Greenplum是否支持。在PostgreSQL中,to_char(date, 'Day')可以返回星期几的名称,但可能带有空格填充,需要使用trim。例如: SELECT trim(to_char(current_date, 'Day')) AS weekday; 这可能适用于Greenplum,从而直接得到名称。 所以,可能的两种方法: 1. 使用EXTRACT(DOW)获取数字,再转换。 2. 使用to_char函数直接获取星期名称。 需要确认哪种方法在Greenplum中有效。根据PostgreSQL的文档,to_char确实支持'Day'格式,返回本地化的星期名称。如果Greenplum也支持,那么这可能更直接。 但用户可能需要数字表示或特定的名称格式。因此,需要提供两种方法,并根据用户的需求选择。 综上,回答应包括: - 使用EXTRACT(DOW FROM date)获取数字,并说明对应的星期几。 - 使用to_char函数获取星期名称,可能需要trim处理。 - 需要调整数字以匹配MySQL的结果,如加1后取模7。 同时,根据引用[2]中的内容,用户可能需要确保Greenplum和MySQL的结果一致,因此可能需要调整Greenplum的输出,例如: 在Greenplum中,使用EXTRACT(DOW FROM date)得到0-6(日-六),而MySQL的DAYOFWEEK()得到1-7(日-六)。如果想让Greenplum的结果与MySQL一致,可以计算(EXTRACT(DOW FROM date) + 1) % 7 + 1,或者直接加1,然后取CASE。或者更简单的,在Greenplum中将结果加1,这样0→1(日),1→2(一)等,然后模7,得到1-7。例如: SELECT CASE (EXTRACT(DOW FROM date_column) + 1) WHEN 1 THEN '星期日' WHEN 2 THEN '星期一' ... END 或者,直接转换: SELECT CASE EXTRACT(DOW FROM date_column) WHEN 0 THEN '星期日' WHEN 1 THEN '星期一' ... END 这取决于用户需要的是数字还是名称,以及是否要与MySQL对齐。 因此,最终的解决方案可能包括两种方法:数字转换和名称转换,并且考虑与MySQL的兼容性。</think>在Greenplum SQL中获取日期对应的星期几可以通过以下两种方法实现: --- ### **方法1:使用EXTRACT函数获取数字形式** `EXTRACT(DOW FROM date)`返回0(星期日)到6(星期六)的整数值: ```sql -- 返回0(周日)到6(周六) SELECT EXTRACT(DOW FROM CURRENT_DATE) AS weekday_num; ``` 若需与MySQL的`DAYOFWEEK()`结果(1=周日,7=周六)对齐,可通过计算调整: ```sql -- 转换为1(周日)到7(周六) SELECT (EXTRACT(DOW FROM CURRENT_DATE) + 1) % 7 + 1 AS mysql_weekday_num; ``` --- ### **方法2:使用to_char函数获取文本形式** 通过格式化函数直接输出星期名称(如“Monday”或中文“星期一”): ```sql -- 返回英文全称(如"Monday") SELECT trim(to_char(CURRENT_DATE, 'Day')) AS weekday_en; -- 返回中文星期(需数据库语言支持中文环境) SELECT CASE trim(to_char(CURRENT_DATE, 'Day')) WHEN 'Monday' THEN '星期一' WHEN 'Tuesday' THEN '星期二' WHEN 'Wednesday' THEN '星期三' WHEN 'Thursday' THEN '星期四' WHEN 'Friday' THEN '星期五' WHEN 'Saturday' THEN '星期六' WHEN 'Sunday' THEN '星期日' END AS weekday_cn; ``` --- ### **与MySQL结果对齐的完整示例** 若需与MySQL的`DAYOFWEEK()`和`DAYNAME()`结果完全一致[^2]: ```sql SELECT (EXTRACT(DOW FROM date_column) + 1) % 7 + 1 AS weekday_num, CASE (EXTRACT(DOW FROM date_column) + 1) % 7 + 1 WHEN 1 THEN '星期日' WHEN 2 THEN '星期一' WHEN 3 THEN '星期二' WHEN 4 THEN '星期三' WHEN 5 THEN '星期四' WHEN 6 THEN '星期五' WHEN 7 THEN '星期六' END AS weekday_cn FROM your_table; ``` ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值