关于postgresql星期的信息

postgresql时间差计算

记一笔postgresql在时间计算上的方法。

因此，可以通过date_part计算两个时间相差几天，几分钟，几秒钟等。

在计算最近几个月，最近几天，最近几个星期的数据时用到

例如：求最近3个月创建的销售量

时间单位：

year :年

week ：该天在所在的年份里是第几周

timezone_minute：时区偏移量的分钟部分

timezone_hour：时区偏移量的小时部分

timezone：与UTC的时区偏移量，以秒记。正数对应 UTC 东边的时区，负数对应 UTC 西边的时区

second ：秒

quarter：日期中年所在季度(1-4)

month：月(0-11)

minute：分钟(0-59)

milliseconds：

isodow：周中的第几天 [1-7] 星期一：1) 星期天：(7)

dow：周中天的索引(0-6 ；星期天是 0)

doy：一年的第几天(1-365/366)

hour：小时(0-23)

day: 天(1-31)

在PostgreSQL 和 Hive中生成日期序列

## 在PostgreSQL 和 Hive中生成日期序列

### Postgresql实现日期序列

在postgresql中，有 generate_series(start_date, end_date, interval)函数来生成日期序列

```

select date(day) as day

from generate_series('2020-05-22'::timestamp, current_date, '1 day'::interval) as day

```

### Hive实现指定指定开始结束日期的日期序列

在Hive中，可以借助 posexplode(list)、datediff(end_date, start_date)来实现。

首先创建一个表名为calender，字段为day，类型为date，存入一个日期数值作为开始日期，比如2014-01-01。

```

CREATE TABLE default.calender (day DATE);

INSERT INTO TABLE default.calender VALUES(to_date('2014-01-01T00:00'));

```

借助 `datediff(end_date, start_date)`、`space(int_count)`、`split(list,seperator)`、`posexplode(list) `生成n个空格，然后split成list，posexplode将行转多列，同时返回index和value。

```

select date_add(day,idx) as new_day from default.calender

lateral view posexplode( split( space( datediff( current_date, to_date('2014-01-01T00:00:00') ) ), ' ')  ) tt as idx, v;

```

中间过程解释：

比如：

```

select datediff('2020-06-30','2020-05-1'); -- 60

select split(space(datediff('2020-06-30','2020-05-1')),' ') -- 生成60个空格，然后split成list

```

index | value

--- | ---

0 | ' '

1 | ' '

2 | ' '

... | ...

59| ' '

#### Hive在日期序列表添加星期几

新增一列存放星期几

```

ALTER TABLE default.calender ADD COLUMNS(weekday STRING);

```

借助函数`datediff`，`pmod`就可以实现

datediff 是两个日期相减的函数

语法：`datediff(string enddate, string startdate)`

返回值: int

说明: 返回两个时间参数的相差天数。

pmod 是正取余函数

语法: `pmod(int a, int b),pmod(double a, double b)`

返回值: int double

说明: 返回正的a除以b的余数

选取一个日期为星期日的日期作为参照日期，这里我选取了2013-12-29

`pmod(datediff( date, '2012-01-01'), 7)  `

返回值：int 0-6

0-6分别表示星期日-星期六

```

INSERT OVERWRITE TABLE default.calender

select date_add(day,idx) as `date`,

    -- 0-6 分别代表星期日-星期六

    case pmod(datediff(date_add(day,idx), to_date('2013-12-29T00:00:00')), 7)

        when 0 then '星期日'

        when 1 then '星期一'

        when 2 then '星期二'

        when 3 then '星期三'

        when 4 then '星期四'

        when 5 then '星期五'

        when 6 then '星期六'

    END as weekday

from default.calender

lateral view posexplode( split( space( datediff( to_date('2030-01-01T00:00:00') , to_date('2014-01-01T00:00:00') ) ), ' ')  ) tt as idx, v;

```

### 补充：Hive实现缺失日期的补全

在统计一些daily的metrics的时候，通常使用group by，往往会存在某些日期没有数据从而导致最后的结果表的日期其实不是连续的齐全序列。

比如：

store_id | date | count

---- | ---- | -----

1 | 2020-04-02 | 45

2 | 2020-04-02 | 10

2 | 2020-04-03 | 10

1 | 2020-04-05 | 50

2 | 2020-04-06 | 10

1 | 2020-04-08 | 50

... | ...... | ....

针对这种情况，需要进行以下步骤拆解：

1. 按照store_id进行聚合，找出最小、最大日期

2. 此时，基于步骤1的CTE表进行基于每个store_id的最小、最大日期的日期序列补全

```

select t.store_id, date_add(t.min_date, idx) as `date`

    from store_with_min_max_usage_date t

    lateral view posexplode(split(space(datediff(t.max_date, t.min_date)),' ')) pe as idx, v

```

3. 将步骤2的结果与之前的agg聚合结果表进行`left join`，对`NULL`用`COALESCE(v, 0)`进行缺失值替换。

如何获取postgreSQL中当前日期是星期几

1、使用Calendar类

/**

* 获取当前日期是星期几br

*

* @param dt

* @return 当前日期是星期几

*/

public static String getWeekOfDate(Date dt) {

String[] weekDays = {"星期日", "星期一", "星期二", "星期三", "星期四", "星期五", "星期六"};

Calendar cal = Calendar.getInstance();

cal.setTime(dt);

int w = cal.get(Calendar.DAY_OF_WEEK) - 1;

if (w 0)

w = 0;

return weekDays[w];

}

2、使用SimpleDateFormat格式化日期

Date date=new Date();

SimpleDateFormat dateFm = new SimpleDateFormat("EEEE");

dateFm.format(date);

注：格式化字符串存在区分大小写

对于创建SimpleDateFormat传入的参数：EEEE代表星期，如“星期四”；MMMM代表中文月份，如“十一月”；MM代表月份，如“11”；

yyyy代表年份，如“2010”；dd代表天，如“25”

数据库中转换数据类型的几种方法

oracle有三种最基本的数据类型，即字符型、数值型、日期型。 (注意需要起别名的地方要起别名)

oracle提供的单行函数中，针对不同的数据类型，提供大量实用的函数，同时提供一系列数据类型转换函数，如下：

sql.append(" select CURVE_NAME,S_YEAR_LIMIT,AVERAGE_VALUE,to_char(d_date,'yyyy-MM-dd') d_date from t_treasury_yield_curve t where d_date = to_date ('"+d_date+"','yyyy-MM-dd')");

24 小时的形式显示出来要用 HH24select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') from dual;

select to_date('2005-01-01 13:14:20','yyyy-MM-dd HH24:mi:ss') from dual;to_date() function。

1、日期格式参数 含义说明

D 一周中的星期几。

DAY 天的名字，使用空格填充到 9 个字符。DD 月中的第几天，DDD 年中的第几天。

DY 天的简写名，IW ISO 标准的年中的第几周，IYYY ISO 标准的四位年份。YYYY 四位年份，

YYY,YY,Y 年份的最后三位，两位，一位。

select sysdate,sysdate - interval '7' month from dual。

当前时间减去 7 年的时间。

select sysdate,sysdate - interval '7' year from dual。

时间间隔乘以一个数字。

select sysdate,sysdate - 8 *interval '2' hour from dual

2、日期到字符操作

select sysdate,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual，select sysdate,to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual，select sysdate,to_char(sysdate,'yyyy-ddd hh:mi:ss') from dual。

select sysdate,to_char(sysdate,'yyyy-mm iw-d hh:mi:ss') from dual。

3、字符到日期操作

select to_date('2003-10-17 21:15:37','yyyy-mm-dd hh24:mi:ss') from dual。

具体用法和上面的 to_char 差不多。

4、TO_NUMBER 

使用TO_NUMBER函数将字符转换为数字

TO_NUMBER(char[, '格式'])

数字格式格式：

9 代表一个数字 。

0 强制显示0 。

$ 放置一个$符 。

L 放置一个浮动本地货币符 。

. 显示小数点 。

, 显示千位指示符。

扩展资料：

在使用数据类型转换之前，同样的 SQL 在有的服务器不报错，主要是数据库的版本不同，当然该过配置的不算。

常见的两种类型转换错误如下：

ERROR: operator does not exist: integer = character varying。

ERROR: operator does not exist: character varying = integer。

字符串转数字，数字转字符串。这一点支持的不如 MySQL 好，当然 PostgreSQL 在新版本中也对这个问题做了改进！

第一种转换方式：使用 CAST 函数。

SELECT * FROM xttblog WHERE id = CAST(888 AS VARCHAR);

SELECT * FROM xttblog WHERE id = CAST('888' AS integer)。

第二种转换方式：使用“::”操作字段。

格式，字段 :: 要转换为的数据类型。

SELECT * FROM xttblog WHERE id = 888 :: VARCHAR;

SELECT * FROM xttblog WHERE id = '888' :: integer。

数据类型转换，虽然看起来很痛苦，但是如果用户的类型用的不对，查询效率会下降。所以，在设计的时候，字段的类型，一定要设计的合理，尤其是在关联表的时候。

我如何确定前一个月使用PostgreSQL的最后一天

是这个吗

!-- 每月最后一天的10点15分触发 --

task:scheduled ref="TaskName" method="execute" cron="0 15 10 L * ?" /

L 表示最后的意思。在日字段设置上，表示当月的最后一天(依据当前月份，如果是二月还会依据是否是润年[leap]), 在周字段上表示星期六，相当于"7"或"SAT"。如果在"L"前加上数字，则表示该数据的最后一个。例如在周字段上设置"6L"这样