Shulou(Shulou.com)06/02 Report--

This article is about how to use date/time functions in PostgreSQL. Xiaobian thinks it is quite practical, so share it with everyone for reference. Let's follow Xiaobian and have a look.

1. Get the current time

select now()

select current_timestamp

select localtimestamp

select clock_timestamp()

Sometimes, we don't need such a complete and meticulous time, we have it naturally.

select current_date

select current_time

select localtime

II. Addition and subtraction of time

Honestly, this is the strangest way to calculate time I've ever seen.

select now() + interval '2 years';select now() + interval '2 year'; select now() + interval '2 y';select now() + interval '2 Y';select now() + interval '2Y';

The result is always the same:

The interval part can be left blank, and so on, and the same is true for month, day, hour, minute, and second addition and subtraction calculations.

AbbreviationMeaningYYearsMMonths (in the date part)WWeeksDDaysHHoursMMinutes (in the time part)SSeconds

It is worth mentioning that writing M alone will default to the addition and subtraction of minutes. For the addition and subtraction of months, it is recommended to write the complete month or months or the abbreviation mon.

Format Function 3.1 Time to String

to_char(timestamp,text)

3.2 String to Date

to_date(text,text)

3.3 String to Time

to_timestamp(text,text)

3.4 Unix timestamp to time

to_timestamp(unixtime)

About the time format of the pattern, lost table run:

Pattern Description HH Hours of Day (01-12)HH12 Hours of Day (01 -12)HH24 Hours of Day (00-23)MI Minutes (00- 59)SS Seconds (00 - 59)MS Milliseconds (000-999)US microseconds (00000 - 99999)AM Noon Mark (capital)Y, YYY Year with comma (4 and more digits) YYY Year (4 and more digits)YYY Last three digits of YY Last two digits of Y MONTH Full length capital month name (9 characters blank)Month Full Length Mixed Case Month Name (9 characters filled in blank)month full length lowercase month name (Blank 9 characters)MON capital abbreviation month name (3 characters)Mon abbreviation mixed case month name (3 characters)mon lowercase month name (3 characters)MM month number (01-12)DAY full-length capital date name (9 characters in blank)Day Full Length Mixed Case Date Name (9 characters filled in blank)day full length lowercase date name (Blank filled with 9 characters)DY abbreviation capital date name (3 characters)Dy abbreviation mixed case date name (3 characters)dy abbreviation lowercase date name (3 characters)DDD days of the year (001-366)DD days of the month (01-31)D days of the week (1-7; Sunday is 1)W Number of weeks in a month (1-5)(Week 1 starts on the first day of the month)WW Number of weeks in a year (1-53)(Week 1 starts on the first day of the year) IV. Some important functions 4.1 Time intervals

age(timestamp, timestamp)

Of course, you can also enter only one parameter to calculate the time interval between current_date and the input parameter.

4.2 date_part(text, timestamp)extract(field from timestamp)

You can also truncate to a specified precision

date_trunc(text, timestamp)

As shown, minutes and seconds after hours are set to 0.

5. Time conversion select timestamp '2012-05-12 18:54: 54';--2012-05-12 18:54:54select date '2012-05-12 18:54: 54'; --2012-05-12select time '2012-05-12 18:54:54'; --18:54:54select TIMESTAMP WITH TIME ZONE '2012-05-12 18:54:54' --2012-05-12 18:54:54+08--Conversion with unix timestamp SELECT TIMESTAMP 'epoch' + 1341174767 * INTERVAL '1 second'; --2012-07-01 20:32:47 Thank you for reading! About "PostgreSQL date/time function how to use" this article is shared here, I hope the above content can be of some help to everyone, so that everyone can learn more knowledge, if you think the article is good, you can share it to let more people see it!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.