Postgres forced type conversion and Time Functions

Source: Internet
Author: User
Tags time zones iso 8601 timestamp to date

I. Type conversion
Postgres type conversion: Usually: used for type conversion, timestamp to date is used more
Select now (): Date
Select now (): varchar

Example 1: varchar of a date is calculated as date.
Select '2017-11-15 16:15:56. 2012 + 08': Timestamp: Date
Select '2017-11-15 16:15:56. 2012 + 08': Date

II. Time type conversion and relative time

// Note the varchar format '2017-11-15 16:15:56. 2012 + 08' In The Future of Java timestamp in SQL. Such a string can calculate the time difference.

If the publishdate of a record in the table is '2017-11-15 16:15:56. 2012 + 08', you can determine whether the record is a publish record in the past 24 hours:

Select extract (epoch from now ()-'2017-11-15 16:15:56. 2012 + 08') <24*377000

Select now ()-'2017-11-15 16:15:56. 2012 + 08' <'24 Hours'

Select now ()-'2017-11-15 16:15:56. 377000 + 08 '<'1 days' or select now ()-'2017-11-16 16:15:56. 377000 + 08 '<'1 Day'

Select now (): Date-'2017-11-15 16:15:56. 2012 + 08': date <1

Note: relative time indicates the time range, which is usually used for statistics and scheduled tasks. In addition to the relative time, 'today' is also used. For example, use publishdate: Date = 'today' to retrieve the record of the current day'

3. The time function extract is used to extract the second value of the absolute time, namely, year, month, and day.

Extract (FieldFromSource)

TheextractFunction retrieves subfields such as year or hour from date/time values.SourceMust be a value expression of typetimestamp,time, Orinterval. (Expressions of TypedateWill be casttimestampAnd can therefore be used as well .)FieldIs an identifier or string that selects what field to extract from the source value.extractFunction returns values of Typedouble precision. The following are valid field names:

The century

SELECT EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13');Result: 20SELECT EXTRACT(CENTURY FROM TIMESTAMP '2001-02-16 20:38:40');Result: 21

The first century starts at 0001-01-01 00:00:00 AD, although they did not know it at the time. this definition applies to all Gregorian calendar countries. there is no century number 0, you go from-1 to 1. postgreSQL releases before 8.0 did not follow the conventional numbering of centuries, but just returned the year field divided by 100.

The day (of the month) field (1--31)

SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40');Result: 16
The year field divided by 10

SELECT EXTRACT(DECADE FROM TIMESTAMP '2001-02-16 20:38:40');Result: 200
The day of the week (0--6; Sunday is 0) ( timestampValues only)

SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40');Result: 5

Note thatextract'S day of the week numbering is different from that ofto_charFunction.

The day of the year (1--365/366) ( timestampValues only)

SELECT EXTRACT(DOY FROM TIMESTAMP '2001-02-16 20:38:40');Result: 47
For dateAnd timestampValues, the number of seconds since 00:00:00-00 (can be negative); intervalValues, the total number of seconds in the interval

SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-08');Result: 982384720SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');Result: 442800

Here is how you can convert an epoch value back to a time stamp:

SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720 * INTERVAL '1 second';
The hour field (0--23)

SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40');Result: 20
The seconds field, including fractional parts, multiplied by 1 000 000. Note that this includes des full seconds.

SELECT EXTRACT(MICROSECONDS FROM TIME '17:12:28.5');Result: 28500000
The millennium


Years in the 1900 s are in the second millennium. the third millennium starts January 1, 2001. postgreSQL releases before 8.0 did not follow the conventional numbering of millennia, but just returned the year field divided by 1000.

The seconds field, including fractional parts, multiplied by 1000. Note that this includes des full seconds.

The Minutes field (0--59)

SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40');Result: 38
For timestampValues, the number of the month within the year (1--12); intervalValues the number of months, modulo 12 (0--11)

SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40');Result: 2SELECT EXTRACT(MONTH FROM INTERVAL '2 years 3 months');Result: 3SELECT EXTRACT(MONTH FROM INTERVAL '2 years 13 months');Result: 1
The quarter of the year (1--4) that the day is in ( timestampValues only)

SELECT EXTRACT(QUARTER FROM TIMESTAMP '2001-02-16 20:38:40');Result: 1
The seconds field, including fractional parts (0-59 (3 ))

SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40');Result: 40SELECT EXTRACT(SECOND FROM TIME '17:12:28.5');Result: 28.5
The Time Zone offset from UTC, measured in seconds. Positive values correspond to time zones east of UTC, negative values to zones west of UTC.
The hour component of the time zone offset
The minute component of the time zone offset
The number of the week of the year that the day is in. by definition (ISO 8601), the first week of a year contains January 4 of that year. the ISO-8601 week starts on Monday .) in other words, the first Thursday of a year is in week 1 of that year. ( timestampValues only) because of this, it is possible for early January dates to be part of the 52nd or 53rd week of the previous year. For example, 2005-01-01Is part of the 53rd week of year 2004, and 2006-01-01Is part of the 52nd week of year 2005.

SELECT EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40');Result: 7
The year field. Keep in mind there is no 0 AD, So subtracting BCYears from ADYears shoshould be done with care.

SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40');Result: 2001

TheextractFunction is primarily intended for Computational processing. for formatting date/time values for display, see section 7.8 data type formatting functions.

Thedate_partFunction is modeled on the traditional INGRES equivalent to the SQL-standard functionextract:

date_part('field', source)

Note that hereFieldParameter needs to be a string value, not a name. The valid field namesdate_partAre the same asextract.

SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');Result: 16SELECT date_part('hour', INTERVAL '4 hours 3 minutes');Result: 4

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.