~drizzle-trunk/drizzle/development

The EXTRACT function retrieves subfields such as day or hour from date/time values. The source value has to be a value expression of type *timestamp*, *time*, *date*, or *interval*.

*Field* is an identifier or string that identifies the field to extract from the source value. The extract function returns values of type *double precision*.

Examples

--------

The following field names are available:

day

^^^

.. code-block:: mysql

SELECT EXTRACT(DAY FROM TIMESTAMP '2011-02-16 20:38:40');

Day of the month.

*Result: 16*

decade

^^^^^^

.. code-block:: mysql

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

The year, divided by 10.

*Result: 200*

dow

^^^

.. code-block:: mysql

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

The day of the week (Sunday is 0, Saturday is 6)

*Result: 5*

doy

^^^

.. code-block:: mysql

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

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

*Result: 47*

hour

^^^^

.. code-block:: mysql

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

The hour field (0 - 23)

*Result: 20*

microseconds

^^^^^^^^^^^^

.. code-block:: mysql

SELECT EXTRACT(MICROSECONDS FROM TIME '17:12:28.5');

The seconds field, including fractional parts, multiplied by 1 000 000; note that this includes full seconds

*Result: 28500000*

minute

^^^^^^

.. code-block:: mysql

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

The minutes field (0 - 59)

*Result: 38*

month

^^^^^

100

For timestamp values, the number of the month within the year (1 - 12).

101

For interval values, the number of months (0 - 11).

102

103

.. code-block:: mysql

104

105

SELECT EXTRACT(MONTH FROM TIMESTAMP '2010-12-29 08:45:27');

106

107

*Result: 12*

108

109

.. code-block:: mysql

110

111

SELECT EXTRACT(MONTH FROM INTERVAL '3 years 4 months');

112

113

*Result: 4*

114

115

.. code-block:: mysql

116

117

SELECT EXTRACT(MONTH FROM INTERVAL '3 years 13 months');

118

119

*Result: 1*

120

121

quarter

122

^^^^^^^

123

124

.. code-block:: mysql

125

126

SELECT EXTRACT(QUARTER FROM TIMESTAMP '2010-12-29 08:45:27');

127

128

The quarter of the year (1 - 4) containing the date.

129

130

*Result: 4*

131

132

second

133

^^^^^^

134

135

.. code-block:: mysql

136

137

SELECT EXTRACT(SECOND FROM TIMESTAMP '2010-12-29 08:45:27');

138

139

The seconds field, including fractional parts (0 - 59)

140

141

*Result: 27*

142

143

.. code-block:: mysql

144

145

SELECT EXTRACT(SECOND FROM TIME '08:15:22.5');

146

147

*Result: 22.5*

148

149

timezone

150

^^^^^^^^

151

152

The time zone offset from UTC, measured in seconds.

153

154

week

155

^^^^

156

157

Returns the week number that a day is in. Weeks are numbered according to ISO 8601:1988.

158

159

ISO 8601:1988 means that if the week containing January 1 has four or more days in the new year, then it is week 1; otherwise it is the last week of the previous year, and the next week is week 1. The ISO-8601 week starts on Monday.

160

161

It's possible for early January dates to be part of the 52nd or 53rd week of the previous year. For example, 2011-01-01 was part of the 52nd week of year 2010.

162

163

.. code-block:: mysql

164

165

SELECT EXTRACT(WEEK FROM TIMESTAMP '2010-01-25 12:44:06');

166

167

*Result: 4*

168

169

year

170

^^^^

171

172

.. code-block:: mysql

173

174

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

175

176

*Result: 2009*

177

178

date_part

179

^^^^^^^^^

180

181

The valid field names for date_part are the same as for extract.

182

183

.. code-block:: mysql

184

185

SELECT date_part('day', TIMESTAMP '2010-07-16 10:12:05');

186

187

*Result: 16*

188

189

.. code-block:: mysql

190

191

SELECT date_part('hour', INTERVAL '5 hours 12 minutes');

192

193

*Result: 4*

Older »