注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

还东国的博客

行之苟有恒,久久自芬芳

 
 
 

日志

 
 

(转载)PostgreSQL Date and Time 的使用8个小例子  

2012-04-18 16:44:53|  分类: 数据库技术 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
 

最近公司使用PostgreSQL ,和原来的SQL和ORACLE有不少区别,今天正好用到这个时间和日期的方法,在网上找了找,有以下的几个小例子不错,转了来。

8 PostgreSQL Date and Time Function Examples

by Balakrishnan Mariyappan on July 8, 2010

Tweet

In this article, let us review following PostgreSQL date and time functions with practical examples.


now()
now()::date
now()::time
date_part()
age()
extract()
date_trunc()
to_char()
to_timestamp()

1. Get Current Date and Time using PostgreSQL now()

Get current date and time using the following query.

dbase=# select now();

             now

------------------------------

 2010-06-19 09:28:43.98216-07

(1 row)

Use the ::time as shown below to get only the time (without date).

dbase=# select now()::time;

      now

----------------

 09:29:12.19297

(1 row)

Use the ::date as shown below to get only the date (without time).

dbase=# select now()::date;

    now

------------

 2010-06-19

(1 row)

2. Get Interval Between Two PostgreSQL Dates

In order to get the number of days between two dates, use the ‘-’ operator. This works like an arithmetic operator.

Let us assume that the employee table contains following records.

# select name,date_of_join from employee;

   name  | date_of_join

--------+------------

 Neeraj | 2002-11-23

 Kiran  | 2003-01-01

 Sam    | 2005-11-23

 John   | 2006-01-01

(4 rows)

Following examples displays number of days between the current date and date_of_join of employees.

dbase=#  select now() - date_of_join as days from employee ;

           days

---------------------------

 2762 days 08:52:33.436868

 2723 days 08:52:33.436868

 1666 days 08:52:33.436868

 1627 days 08:52:33.436868

(4 rows)

You can also subtract numeric value from the date. This subtracts number of days from a specific date. The following query subtracts 7 days from the date_of_join field value.

dbase=# select date_of_join – 7 as output from employee;

   output

------------

 2002-11-16

 2002-12-25

 2005-11-16

 2005-12-25

(4 rows)

3. Round the interval (above difference) to the nearest day using date_part()

Use the following query to round the number of days to the nearest day.

dbase=# select date_part('days', now() - date_of_join) as days from employee;

 days

 ------

 2762

 2723

 1666

 1627

(4 rows)

4. Breakdown the date interval into number of years, months and days using age()

There is the another way to find the interval between the current date and date_of_join as shown below.

dbase=# select age(date_of_join) from employee;

          age

------------------------

 7 years 6 mons 23 days

 7 years 5 mons 15 days

 4 years 6 mons 23 days

 4 years 5 mons 15 days

(4 rows)

5. Retrieve any sub-fields from the Timestamp using PostgreSQL extract()

Postgres date functions allows you to extract the specific sub-field form the date. Following query extracts the year from date_of_join field.

dbase=# select extract(year from date_of_join) as output from employee;

 output

--------

   2002

   2003

   2005

   2006

(4 rows)

6. Truncate a particular date field using PostgreSQL date_trunc()

Postgres provides the facility to truncate the date to specific precision. The following query gives you start date of the month based on the value in the date_of_join field.

dbase=# select date_trunc('month',date_of_join) as output from employee ;

       output

------------------------

 2002-11-01 00:00:00-08

 2003-01-01 00:00:00-08

 2005-11-01 00:00:00-08

 2006-01-01 00:00:00-08

(4 rows)

7. Display Postgresql Date in Various Format using to_char()

The following query displays the date in “dd/mm/yy” format.

dbase=# select to_char(date_of_join,'mm/dd/yy') as output from employee;

 output

----------

 11/23/02

 01/01/03

 11/23/05

 01/01/06

(4 rows)

The month and day of date_of_join field can be displayed as shown below.

dbase=# select to_char(date_of_join, 'FMMonth FMDDth') as output from employee;

   output

---------------

 November 23rd

 January 1st

 November 23rd

 January 1st

(4 rows)

Display the full abbreviation of day and month as shown below.

dbase=# select to_char(startdate, 'Dy (Day), Mon (Month)') as output from employee;

              output

----------------------------------

 Sat (Saturday ), Nov (November )

 Wed (Wednesday), Jan (January  )

 Wed (Wednesday), Nov (November )

 Sun (Sunday   ), Jan (January  )

(4 rows)

8. Convert String to Date using PostgreSQL to_timestamp()

Postgres provides the way to convert a string value into proper date format as shown below.

dbase=# select to_timestamp('201024June10:12am', 'YYYYDDFMMonthHH12:MIam') as valid_time;

       valid_time

------------------------

 2010-06-24 10:12:00-07

(1 row)

  评论这张
 
阅读(3765)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017