Headaches Digital Piano Dizziness

PostgreSQL: Extract fields from a PostgreSQL

One of the most stored in the database are the date, there are times when you should get some date fields to be used for this table People created as shown below:

create table people (
   id                     SERIAL      NOT NULL,
   rut                   varchar(15) NULL,
nombre varchar (30) NULL,
apaterno varchar (30) NULL,
amaterno varchar (30) NULL,
fecha_nac dating NULL,
   hora_nac         time NULL,
   sexo                 varchar(1)  NULL,
   direccion         varchar(30) NULL,
   ciudad             varchar(30) NULL,
   telefono           integer     NULL,
   created            timestamp   NOT NULL,
   modified          timestamp   NULL, constraint
pk_personas primary key (id)
)

be rescued from this table each of the fields in the creation of separate registration for others and get the day of the week corresponding date record creation. SELECT


rut,
extract (year from created):: int as anyo,
extract (month from created):: int as month,
extract (day from created):: int as day
extract (hour from created):: int as hour,
extract (minute from created):: int as minute,
extract (second created from):: int as second
CASE extract (dow from created)
WHEN 0 THEN 'Sunday' WHEN 1 THEN
'Monday' WHEN 2 THEN
'Tuesday' WHEN
3 THEN 'Wednesday' WHEN 4 THEN
'Thursday' WHEN 5 THEN
'Friday' WHEN 6 THEN
'Saturday' END


dow FROM
as people
;

With this query you get all the date of creation completely broken, besides giving the day of the week that was created using CASE to choose from among the values \u200b\u200bobtained value and converts it into a string with the name of the day.

extract (field from source)

Retrieves the values \u200b\u200bof a date / time, where the source is a field of data type date / time, in this case including a timestamp, while the field is the type of value recovered from the date the existing fields in the variables of time stamps are:

• century: the century of the return date, there century 0, only exists from -1 to 1. Eg 2000 is the 20 th century, while 2001 is the 21 st century.
• day: returns the day of the month, going from 1 to 31 according to the corresponding month.
• Decade: divide the field year at 10. Ie the year 2001 would be the decade 200.
• dow: returns the day of the week ranging from 0 to 6, where 0 corresponds to Sunday.
• I: Returns the day of the year which runs from 1 to 365/366 as appropriate.
• epoch: returns the number of seconds since 1/1/1970. This value can be negativo.También can be used to obtain the number of seconds in a time interval. Eg SELECT EXTRACT (EPOCH FROM INTERVAL'5 day 3 hours');
• hour: returns the time of the date, this value is between 0 and 23.
• microseconds: The time in seconds, including the fractional part multiplied by 1,000,000.
• millennium: Returns the value of the millennium year. Ie the year 1990 is the millennium 2, 2001 is the millennium 3.
• milliseconds: Returns the seconds of time field multiplied by 1,000.
• minute: Returns the minutes of the field, this value is defined between 0 and 59.
• month: For timestamp values, returns the number of the month without the year, values \u200b\u200bare defined between 0 and 12. It also allows intervals of months. Eg SELECT EXTRACT (MONTH FROM INTERVAL '2 years 3 months');
• quarter: Divide the year and returns four values \u200b\u200bbetween 1 and 4, as the interval that is the day of the year.
• second: returns the number of seconds including the fractional part of the field.
• timezone: UTC time zone, measured in seconds, positive values \u200b\u200bcorrespond to the time zone east of UTC, negative values \u200b\u200bto the time zone west of UTC.
• timezone_hour: Time for time zone.
• timezone_minute: The minutes for the zone.
• week: The number of days of the year.
• year: returns the year of the date, no year 0, so there years before Christ and after Christ.

"Thank you for sharing your knowledge"