How to insert time and date in mysql. MYSQL date and time formatting functions
MySql is not just an attractive system for organizing data storage, but also an EXCELLENT TOOL for processing information.
Today we will look at working with the mysql functions: Date, DateTime. I will also talk about the mysql methods used to convert a string to a date. In general, there are different approaches to solving problems related to dates in the mysql DBMS.
Problem.
Let's imagine you are writing a system for sending SMS notifications to your customer base.
Table 1. tbl_clients* - table with customer data (full name and date of birth), data on the quantity, type of goods and application processing status.
Table 2. tbl_sms – table of SMS statuses and the connection of the client id with the date the status was received.
Closer to combat. We arm ourselves with built-in functions.
The tbl_sms table contains several records for one Id_clients, with different statuses and times of receipt. For example:
Let's assume that the boss requires a list of the latest statuses for all clients. You can quickly solve the problem using built-in functions. The sql query for this case looks like this:
SELECT * FROM tbl_sms ts JOIN (SELECT Id_clients, max(Date_status) as max_dat FROM tbl_sms GROUP BY Id_clients) ts_max ON ts. id_clients = ts_max. id_clients and ts. Date_status= ts_max.max_dat ;
The result of the query is a selection of records with the maximum date for each Id_clients:
The request is complex, I didn’t understand it the first time, but I’ll try to explain. In a query, we join two tables. The first tbl_sms contains all the data without taking into account the conditions, so to speak in “raw form”. The second table is the result of the query
SELECT Id_clients, max(Date_status) as max_dat FROM tbl_sms GROUP BY Id_clients ;
to the same tbl_sms using MAX functions() and grouping by the Id_clients field.
In the second table, we group records by id_clients using the “GROUP BY Id_clients” construct. That is, now we have one record (although there are several records in the table for one id_clients value) - this is one value of the id_clients field. In SELECT we use the MAX() function, the result of which is to select the maximum value recorded in the table field. For reference, in the mysql DBMS there is an inverse function MIN(). As a result, in a query using the MAX() function, we get the maximum value of the Date_status field for each value of the id_clients field.
And finally, selecting records that meet the following conditions: “ON ts. id_clients = ts_max. id_clients and ts. Date_status= ts_max.max_dat”, we get the desired result.
The boss is pleased. But then an accountant comes and sets a new task: “We need to select all the statuses that we received only for 10/05/2014.”
We write the following request:
SELECT * FROM tbl_sms WHERE Date_status>’2014-10-05 00:00:00’ And Date_status<’2014-10-05 23:59:59’ ;
Since the data type of the Date_status field is DATETIME, we therefore set the following conditions: the value From 00:00 to 23:59 10/05/2014 – this period includes exactly one day or 24 hours. You should pay attention to the form of recording the date. In the Date data type in mysql, we represent the date in the following format Year(4)-Month(2)-Day(2). If the “status” field were in DATE format, the request would look like this:
SELECT * FROM tbl_sms WHERE Date_status="2014-10-05";
We have figured out the accountant's task. The reports have been submitted. Now let's look at working with dates in mysql from a different angle - if the date is represented as a string. Sometimes it happens…
Date in type VARCHAR or CHAR... What to do? Using the string conversion function.
I myself have encountered a similar problem more than once. The fact is that if you have a date in the form of a string, then all the queries described above become useless. For example, you need to select all clients whose Date_Zap field values lie in a certain date period. Don’t list all the dates... Of course not. There is always a way out, and built-in functions will help us out again. For such a task in mysql, there is a function STR_TO_DATE().
As the name suggests, the function converts a string into a date. The arguments to the STR_TO_DATE function are a date in a CHAR or VARCHAR data type, and the date format YOU PASS IN.
For example, you would query the tbl_clients table:
SELECT Date_Zap, STR_TO_DATE(Date_Zap,’%d.%m.%Y’) FROM tbl_clients WHERE id_clietns=’345’;
The result of using the STR_TO_DATE() function:
As you can see, from the usual date format we get a mysql format date. A few comments about the request:
- If you are not completely sure of the result of the function, always do as here to see the field values before and after the transformation. This will save you nerves.
- If you are going to use the result, for example, in a program and refer specifically to the field name, then I advise you to replace the long name “STR_TO_DATE(Date_Zap,’%d.%m.%Y’)”, for example, with the name Data_Mysql, for this we modify the query
The result of using the function converting a string to a mysql date format (STR_TO_DATE()):
Display the date in words.
Often there is a need to ensure that when creating reports from a database (mysql), the date on documents should be in the format “Day Month in words Year”. For example: there is “10/21/2014”, but you need “October 21, 2014”. When I encountered such a problem, I wrote an entire processing in Delphi to generate the date. It turned out that the mysql DBMS already has a built-in function for converting a date from a numeric representation to a date in words. And, as I said at the beginning, mysql is not only a data storage management tool, but also an information processing tool.
So, it’s easy to get a date in the format described above in a simple request:
SELECT DATE_FORMAT('2014-10-21','%d %M %Y') as Data_Mysql; //Note the spelling %M
Everything would be fine, the date is displayed in words, but the name of the month is in English. This can happen if the server has a global variable lc_time_names=en_EN. The value for the Russian date will be equal to: lc_time_names=ru_RU.
There are several options for solving this problem.
The first option is to set the default value in the mysql server configuration. To do this, open my.ini in the directory where the mysql server is installed, and in the “” section, insert the line “lc_time_names = ‘ru_RU””.
The second option will suit you if there is no direct access to the mysql configuration. The value of the variable can be changed when executing an sql query. Just do the following before the request: “SET lc_time_names = ‘ru_RU’;”. With this command you set the value of the lc_time_names variable in this session with the mysql server. That is, if you lose connection with the mysql server, when you reconnect, it will be set to the default value “en_EN”.
Therefore, I advise you to use the command immediately before executing the sql query. In this case, the final query will look like:
SET lc_time_names = "ru_RU"; SELECT DATE_FORMAT('2014-10-21','%d %M %Y') as 'Data_Mysql';
Result of the sql query using the DATE_FORMAT() function:
As you can see, everything has become correct, the name of the month has become Russian.
Thus, using mysql functions, it is easy and fast to process information. I advise you not to do work that you can delegate to mysql tools. This way you have to write less code, and in 90% of cases such a system will work more stably. Tested from my own experience. I hope this article will be useful to you, Dear Readers! Best regards, your Shinobi.
For a description of the range of values for each type and possible date and time formats, see section 6.2.2 Date and Time Data Types.
Below is an example that uses date functions. The following query selects all records with date_col value within the last 30 days:
Mysql> SELECT something FROM tbl_name WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) DAYOFWEEK(date) Returns the index of the day of the week for the date argument (1 = Sunday, 2 = Monday, ... 7 = Saturday). These index values follow the ODBC standard: mysql> SELECT DAYOFWEEK("1998-02-03"); -> 3 WEEKDAY(date) Returns the index of the day of the week for the date argument (0 = Monday, 1 = Tuesday, ... 6 = Sunday): mysql> SELECT WEEKDAY("1997-10-04 22:23:00"); -> 5 mysql> SELECT WEEKDAY("1997-11-05"); -> 2 DAYOFMONTH(date) Returns the ordinal number of the day of the month for the date argument in the range from 1 to 31: mysql> SELECT DAYOFMONTH("1998-02-03"); -> 3 DAYOFYEAR(date) Returns the ordinal number of the day of the year for the date argument in the range from 1 to 366: mysql> SELECT DAYOFYEAR("1998-02-03"); -> 34 MONTH(date) Returns the serial number of the month in the year for the date argument in the range from 1 to 12: mysql> SELECT MONTH("1998-02-03"); -> 2 DAYNAME(date) Returns the name of the day of the week for the date argument: mysql> SELECT DAYNAME("1998-02-05"); -> "Thursday" MONTHNAME(date) Returns the name of the month for the date argument: mysql> SELECT MONTHNAME("1998-02-05"); -> "February" QUARTER(date) Returns the quarter number of the year for the date argument in the range from 1 to 4: mysql> SELECT QUARTER("98-04-01"); -> 2 WEEK(date) WEEK(date,first) Given one argument, returns the ordinal number of the week in the year for date, ranging from 0 to 53 (yes, possibly the beginning of the 53rd week) for regions where Sunday is considered the first day of the week . The WEEK() form with two arguments allows you to specify whether the week begins on Sunday or Monday. The week starts from Sunday if the second argument is 0, and from Monday if 1: mysql> SELECT WEEK("1998-02-20"); -> 7 mysql> SELECT WEEK("1998-02-20",0); -> 7 mysql> SELECT WEEK("1998-02-20",1); -> 8 mysql> SELECT WEEK("1998-12-31",1); -> 53 Note: In version 4.0, the WEEK(#,0) function was changed to match the US calendar. YEAR(date) Returns the year for the date argument in the range 1000 to 9999: mysql> SELECT YEAR("98-02-03"); -> 1998 YEARWEEK(date) YEARWEEK(date,first) Returns the year and week for date . The second argument to this function works similar to the second argument to the WEEK() function. Please note that the year may differ from that specified in the date argument for the first and last weeks of the year: mysql> SELECT YEARWEEK("1987-01-01"); -> 198653 HOUR(time) Returns the hour for the time argument in the range 0 to 23: mysql> SELECT HOUR("10:05:03"); -> 10 MINUTE(time) Returns the number of minutes for the time argument in the range from 0 to 59: mysql> SELECT MINUTE("98-02-03 10:05:03"); -> 5 SECOND(time) Returns the number of seconds for the time argument in the range from 0 to 59: mysql> SELECT SECOND("10:05:03"); -> 3 PERIOD_ADD(P,N) Adds N months to period P (in YYMM or YYYYMM format). Returns a value in YYYYMM format. Note that the period argument P is not a date value: mysql> SELECT PERIOD_ADD(9801,2); -> 199803 PERIOD_DIFF(P1,P2) Returns the number of months between periods P1 and P2. P1 and P2 must be in YYMM or YYYYMM format. Note that the period arguments P1 and P2 are not date values: mysql> SELECT PERIOD_DIFF(9802,199703); -> 11 DATE_ADD(date,INTERVAL expr type) DATE_SUB(date,INTERVAL expr type) ADDDATE(date,INTERVAL expr type) SUBDATE(date,INTERVAL expr type) These functions perform arithmetic operations on dates. Both are new in MySQL 3.22. The ADDDATE() and SUBDATE() functions are synonyms for DATE_ADD() and DATE_SUB() . In MySQL 3.23, you can use the + and - operators instead of the DATE_ADD() and DATE_SUB() functions if the right-hand expression is a column of type DATE or DATETIME (see example below). The date argument is a DATETIME or DATE value that specifies the start date. The expr expression specifies the amount of interval to add to or subtract from the start date. The expr expression is a string that can begin with - for negative interval values. The type keyword indicates how a given expression should be interpreted. The EXTRACT(type FROM date) helper function returns an interval of the specified type (type) from a date value. The following table shows the relationship between the type and expr arguments:
| Meaning Type | Expected Format expr |
| SECOND | SECONDS |
| MINUTE | MINUTES |
| HOUR | HOURS |
| DAY | DAYS |
| MONTH | MONTHS |
| YEAR | YEARS |
| MINUTE_SECOND | "MINUTES:SECONDS" |
| HOUR_MINUTE | "HOURS:MINUTES" |
| DAY_HOUR | "DAYS HOURS" |
| YEAR_MONTH | "YEARS-MONTHS" |
| HOUR_SECOND | "HOURS:MINUTES:SECONDS" |
| DAY_MINUTE | "DAYS HOURS:MINUTES" |
| DAY_SECOND | "DAYS HOURS:MINUTES:SECONDS" |
| Determinant | Description |
| %M | Name of the month (January...December) |
| %W | Name of the day of the week (Sunday...Saturday) |
| %D | Day of the month with English suffix (1st, 2nd, 3rd, etc.) |
| %Y | Year, date, 4 digits |
| %y | Year, date, 2 digits |
| %X | Year for a week where Sunday is considered the first day of the week, number, 4 digits, used with "%V" |
| %x | Year for a week where Sunday is considered the first day of the week, number, 4 digits, used with "%v" |
| %a | Abbreviated name of the day of the week (Sun...Sat) |
| %d | Day of the month, number (00..31) |
| %e | Day of the month, number (0..31) |
| %m | Month, date (01..12) |
| %c | Month, date (1..12) |
| %b | Abbreviated name of the month (Jan...Dec) |
| %j | Day of the year (001..366) |
| %H | Hour (00..23) |
| %k | Hour (0..23) |
| %h | Hour (01..12) |
| %I | Hour (01..12) |
| %l | Hour (1..12) |
| %i | Minutes, number (00..59) |
| %r | Time, 12-hour format (hh:mm:ss M) |
| %T | Time, 24-hour format (hh:mm:ss) |
| %S | Seconds (00..59) |
| %s | Seconds (00..59) |
| %p | AM or PM |
| %w | Day of the week (0=Sunday..6=Saturday) |
| %U | Week (00..53), where Sunday is considered the first day of the week |
| %u | Week (00..53), where Monday is considered the first day of the week |
| %V | Week (01..53), where Sunday is considered the first day of the week. Used with `%X" |
| %v | Week (01..53), where Monday is considered the first day of the week. Used with `%x" |
| %% | Literal `%" . |
User Comments
| Posted by on Thursday June 20 2002, @7:08am | [Delete] [Edit] |
When selecting a timestamp datatype from a table
and want to adjust to a timezone (this example is
from pacific time to EST):
SELECT date_format(DATE_ADD(,
INTERVAL 3 HOUR), "%M %e, %Y at %H:%i EST") FROM
;
| Posted by Dan Allen on Tuesday June 25 2002, @12:50am | [Delete] [Edit] |
| Posted by on Wednesday December 18 2002, @5:28pm | [Delete] [Edit] |
I have to wonder why there is no function that
does basically what this does:
SELECT FLOOR((UNIX_TIMESTAMP(NOW()) -
UNIX_TIMESTAMP(date_of_birth))/60/60/24/364.25)
as age
It would make a lot of code look a lot
cleaner.
This is even of particular importance for use
on
storing o collecting data about people in the US,
as US law prohibits collecting personal
information about anyone under 13 years of age,
and this trick figures out their age in years.
It would be a lot nicer with an AGE(date
[,date2]
)function.
The closest thing in there is period_diff,
which
doesn"t accept a standard date string and returns
months, which are
useful
Actually, this doesn't work in the case of pre-
epoch birthdates, worse yet. unix_timestamp
returns 0 for all pre-epoch dates.
I contend that that is a bug and really needs
to
be fixed.
Have to use period_diff/12 I guess.
| Posted by Matthew Mullenweg on Wednesday December 18 2002, @5:27pm | [Delete] [Edit] |
You bring up some important issues, but
dealing with ages really isn't that
hard. For
example you could do something like this:
Mysql> SELECT DATE_FORMAT(
FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(dob)), "%Y")+0
AS age FROM people;
Where "dob" is obviously their date of birth.
It"ll also work with pre and post-epoch dates.
Please excuse the funky formatting as the
comment system seems to insist on inserting line
breaks into the code block. I
ran into this problem while working on some
genealogical things over at href="http://www.mullenweg.com" >Mullenweg.com
, a family site. I hope this helps!
| Posted by Dan Allen on Wednesday December 18 2002, @5:31pm | [Delete] [Edit] |
Seems to be a real pain to get the days in the
month, but here is one way
select
DATE_FORMAT(CONCAT(YEAR("2002-05-05"), "-",
MONTH("2002-05-05" + INTERVAL 1 MONTH), "-01") -
INTERVAL 1 DAY, "%e") as numDays
I guess it would be nice if we could just have a
DATE_FORMAT entity for this
| Posted by Isaac Shepard on Wednesday December 18 2002, @5:31pm | [Delete] [Edit] |
If you"re looking for generic SQL queries that will
allow you to get the days, months, and years
between any two given dates, you might consider
using these. You just need to substitute date1 and
date2 with your date fields and mytable with your
table name.
Number of days between date1 and date2: SELECT TO_DAYS(date2) - |
Number of months between date1 and date2: SELECT PERIOD_DIFF |
Number of years between date1 and date2: SELECT (YEAR(date2) - YEAR |
Now for some comments about these.
1. These results return integer number of years,
months, and days. They are "floored." Thus, 1.4
days would display as 1 day, and 13.9 years would
display as 13 years.
2. Note that I use boolean expressions in some
cases. Because boolean expressions evaluate to 0
or 1, I can use them to subtract 1 from the total
based on a condition.
For example, to calculate the number of years
between to dates, first simply subtract the years.
The problem is that doing so isn't always correct.
Consider the number of years between July 1, 1950
and May 1, 1952. Technically, there is only one full
year between them. On July 1, 1952 and later,
there will be two years. Therefore, you should
subtract one year in case the date hasn't yet
reached a full year. This is done by checking the if
the second month-day is before the first month-
day. If so, this results in a value of 1, which is
subtracted from the total.
3. To get the month-day, I use MID. This is better
than using RIGHT, since it will work for both dates
and datetimes.
4. As mentioned in a previous post, PERIOD_DIFF
needs yearmonth format, but there is really no best
way to do this. To get this, I use DATE_FORMAT
(date1,"%Y%m").
5. Unlike many other solutions, these queries should
work with dates prior to 01/01/1970.
6. Feedback would be much appreciated. Since I"m
using this in my own applications, I would be happy if
you let me know if you discover an error in the logic.
| Posted by Jason Rust on Wednesday December 18 2002, @5:31pm | [Delete] [Edit] |
A couple other times between functions. This is
another way to calculate the months between two
dates which may come in handy at times as it is
linear:
Months Between 2002-02-15 and
2002-01-15
(((YEAR("2002-02-15") - 1) * 12 +
MONTH("2002-02-15")) - ((YEAR("2002-01-15") - 1) *
12 + MONTH("2002-01-15"))) - (MID("2002-01-15", 9,
2) < MID("2002-02-15", 9, 2))
The following is a weeks between function:
Weeks Between 2002-08-28 and
2002-08-21
FLOOR((TO_DAYS("2002-08-28") -
TO_DAYS("2002-08-21")) / 7)
| Posted by on Thursday September 12 2002, @6:22am | [Delete] [Edit] |
You can"t do DATE_ADD("15:30:00", INTERVAL 55
MINUTE), it will return NULL, the workaround I found
is:
DATE_FORMAT(DATE_ADD(CONCAT("2000-01-
01 ",`time_field`),INTERVAL "minutes" MINUTE), "%
H:%i:%s")
| Posted by Marcelo Celleri on Tuesday September 17 2002, @2:58pm | [Delete] [Edit] |
I have two datetime fields, (date_out, date_in) ,
they"re records of loggin and logout times and I
need to find the way to get the difference between
the two of them, and I tried this one: f_out -
f_in but it gave me an integer result that is
worthless for me, I need the difference in
seconds Could you please help me , cause I don"t
have an idea how to convert this answer to seconds
| Posted by Ricky Orea on Tuesday November 12 2002, @3:51pm | [Delete] [Edit] |
My user inputs a date in the format
of "MM/DD/YYYYY", how can I convert it to the
format "YYYY/MM/DD" before I save it on a mysql
table?
| Posted by Ram Narayan on Monday November 18 2002, @8:46pm | [Delete] [Edit] |
Hi All,
Adding to my friend Ricky Orea's query, If my user
enters the date in dd mmm yyyy format(26 nov
2002), how should I insert into the mysql db.
| Posted by louis bennett on Thursday November 21 2002, @11:35am | [Delete] [Edit] |
%d Day of the month, numeric (00..31)
%e Day of the month, numeric (0..31)
Not to be pedantic, but there is never a day "0"
in a month...
| Posted by Twidi on Wednesday December 18 2002, @5:27pm | [Delete] [Edit] |
How to obtain number of days in a month:
Just take the first day of the month, add one
month (to get the first day of the next month) and
substract one day (to get the last day of the
previous month, that is the number of days), as
follow:
Select
dayofmonth(date_sub(date_add(concat(date_format(MYDATE,
"%Y-%m"), "-01"), interval 1 month), interval 1
day)) as number_of_days from MYTABLE;
(just replace MYDATE and MYTABLE)
Perhaps there"s an other way...
| Posted by on Sunday December 1 2002, @10:46am | [ on Wednesday January 8 2003, @5:25am | [Delete] [Edit] |
I am trying to write a MySQL script that will populate a table with records for each value between 2 given parameters. Without the loop structure available in
Stored Procedures, this is proving problematic. Does anyone have a solution?
Rough Example:-
Table "test" has a date field "test_date".
To begin with, I want to touch on the topic of what format is best to store dates in the database: TIMESTAMP or DATETIME. This question has been and is being raised many times on forums, blogs, etc. But in order not to immediately send you to search engines, I’ll try in simple words and show the difference with an example. Type DATETIME- stores the date value in the format "YYYY-MM-DD HH:MM:SS" and does not depend on the time zone. TIMESTAMP- stores a timestamp, i.e. the number of seconds that have passed since January 1, 1970. MySQL converts these values taking into account the current time zone both when writing to the database and when outputting from it. What does this mean...
For example, you have just added an article to the database, on your calendar it is the first of January 2014, and on your watch it is 01:00. If the date field is of type DATETIME, then everyone who visits the site will see exactly this date and time, regardless of their place of residence. Everything seems to be fine, but the user ( let's call him "Bill G"), living somewhere in New York, the first of January has not yet arrived - for him it is December 31, 2013 and the clock shows 19:00. He is slightly perplexed, because... celebrate New Year it hasn’t started yet, but it’s already imagining “an article from the future” ;) This won’t happen with the TIMESTAMP type, because When outputting, its time zone will be taken into account.
“Everything is clear!”, you say and quickly change all the date fields to TIMESTAMP type, and Bill G will breathe a sigh of relief, but not for long. When registering on your site, Bill indicated the date and time of his birth. Traveling around the world, he always looks at your website and discovers with horror that the time, and sometimes the date of his birth, is always different, because... are displayed taking into account the time zone in which he is currently located. Yes, in this case, the TIMESTAMP type played a cruel joke.
We conclude that for certain tasks, you need to select the appropriate field type or control the recording/output depending on the desired result.
Let's move on to popular problems and options for solving them. Select records within the specified date range, i.e. for a certain period of time.
SELECT * FROM `table_name` WHERE `date_field` BETWEEN "2014-07-05" AND "2014-07-15" ORDER BY `date_field`;
All records will be selected where the dates in the "date_field" field will be in the range from July 5, 2014 to July 15, 2014, including the specified dates. We must not forget that by default dates in MySQL are stored in the format "YYYY-MM-DD HH:MM:SS" and, accordingly, the format mask is "%Y-%m-%d %H:%i:%s" (standard ISO). How to resolve the issue if the date does not come in this format? Let's discard the PHP options and see how this can be done in the request itself. And for such purposes, we will need the function STR_TO_DATE(). Syntax: STR_TO_DATE(str, format), Where " str" - date string and " format" is the format corresponding to it. Let's test:
SELECT STR_TO_DATE("12/31/2013", "%d.%m.%Y"); /* "2013-12-31" */ SELECT STR_TO_DATE("31/12/13 13:50", "%d/%m/%y %H:%i"); /* "2013-12-31 13:50:00" */
The result of the execution is a date in the format that is used by default in MySQL. That is, we need to specify not the format in which we want to receive the output date, but the format in which we provide the date for processing. Using this method, our entry above could even look like this:
SELECT * FROM `table_name` WHERE `date_field` BETWEEN STR_TO_DATE("07/05/2014", "%d.%m.%Y") AND STR_TO_DATE("July 15, 2014", "%M %d,%Y") ORDER BY `date_field`;
Since we’ve touched on the issue of date formatting, let’s look at how to get a date when sampling in the format we need, because Many people are much more accustomed to seeing “12/31/2014” or “December 31, 2014” than “2014-12-31”. For such purposes, use the function DATE_FORMAT(). Syntax: DATE_FORMAT(date, format), Where " date" - date string and " format" - the format to be converted to " date". Unlike the STR_TO_DATE() function, we ourselves indicate the desired output format, but the date must be specified in ISO format, i.e. "YYYY-MM-DD HH:MM:SS". We check:
SELECT DATE_FORMAT("2014-12-31", "%d.%m.%Y"); // 12/31/2014 SELECT DATE_FORMAT("2014-12-31", "%d %M %Y"); // 31 December 2014
If we were communicating with you in real time, then at this point, most likely, the question would immediately follow: " But how to display the month in another language: Ukrainian, Russian or Chinese, after all?"It’s very simple - install the required locale. And this can be done either in configuration file MySQL (my.cnf), or simply a request from PHP, after connecting to the database and before the main queries:
SET lc_time_names = ru_RU; SELECT DATE_FORMAT("2014-12-31", "%d %M %Y"); // result: December 31, 2014 // if desired, you can also add “g.” or "year" SELECT DATE_FORMAT("2014-12-31", "%d %M %Y year"); // result: December 31, 2014
Beauty! ;) And a few more examples of requests that are also often needed, but cause confusion among beginners.
// Select records for the current day SELECT * FROM `table_name` WHERE `date_field` >= CURDATE(); // All records for yesterday SELECT * FROM `table_name` WHERE `date_field` >= DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND `date_field` NOW() - INTERVAL 30 DAY; // Select all for specific month current year (for example, for the month of May) SELECT * FROM `table_name` WHERE YEAR(`date_field`) = YEAR(NOW()) AND MONTH(`date_field`) = 5; // or for the month of May, but in 2009 SELECT * FROM `table_name` WHERE YEAR(`date_field`) = 2009 AND MONTH(`date_field`) = 5;
I don’t see the point in describing in detail the MySQL functions used in the examples, because... they are intuitive and for a person with at least a little knowledge of English it will not be difficult to understand that, for example, the function MONTH() returns the month of the date, YEAR()- her year, and DAY() (or synonym DAYOFMONTH()) - day. Keyword INTERVAL- serves for arithmetic operations on dates and their changes.
SELECT "2014-07-07 23:59:59" + INTERVAL 1 SECOND; // result: 2014-07-08 00:00:00 SELECT "2014-07-07 23:59:59" + INTERVAL 1 DAY; // result: 2014-07-08 23:59:59 // the same thing. but using the DATE_ADD() function SELECT DATE_ADD("2014-07-07 23:59:59", INTERVAL 1 DAY); // 2014-07-08 23:59:59 // If you need to subtract rather than add SELECT DATE_SUB("2014-07-07 23:59:59", INTERVAL 1 DAY); // 2014-07-06 23:59:59 // or simply SELECT "2014-07-07 23:59:59" - INTERVAL 1 DAY; // 2014-07-06 23:59:59
These are not all functions for working with dates, and I would advise you to go through them for informational purposes on the official website in order to know about their existence if a non-standard situation arises. But I want to hope that even this short review MySQL functions for working with dates in this article will help you navigate the situation and make the right decision. If difficulties still arise, then ask questions in this topic or the “Your question” section. We'll figure it out together ;)
Below is an example that uses date functions. The following query selects all records with date_col value within the last 30 days:
Mysql> SELECT something FROM tbl_name WHERE TO_DAYS(NOW()) - TO_DAYS(date_col)<= 30;
Returns the index of the day of the week for date (0 = Monday, 1 = Tuesday, ... 6 = Sunday):
Mysql> SELECT WEEKDAY("1998-02-03 22:23:00"); -> 1 mysql> SELECT WEEKDAY("1997-11-05"); -> 2
DAYOFMONTH(date)
Returns the ordinal number of the day of the month for the date argument, in the range from 1 to 31:
Mysql> SELECT DAYOFMONTH("1998-02-03"); -> 3
Returns the ordinal number of the day of the year for the date argument, in the range from 1 to 366:
Mysql> SELECT DAYOFYEAR("1998-02-03"); -> 34
Returns the serial number of the month in the year for the date argument, ranging from 1 to 12:
Mysql> SELECT MONTH("1998-02-03"); -> 2
Returns the name of the day of the week for the date argument:
Mysql> SELECT DAYNAME("1998-02-05"); -> "Thursday"
Returns the name of the month for the date argument:
Mysql> SELECT MONTHNAME("1998-02-05"); -> "February"
Returns the quarter number of the year for the date argument, ranging from 1 to 4:
Mysql> SELECT QUARTER("98-04-01"); -> 2
WEEK(date) , WEEK(date,first)
Given one argument, returns the week ordinal number of the week in the year for date, ranging from 0 to 53 (yes, possibly the beginning of the 53rd week) for regions where Sunday is considered the first day of the week. The WEEK() form with two arguments allows you to specify whether the week begins on Sunday or Monday. The result will be in the range 0-53 or 1-52.
Here's how the second argument works:
Mysql> SELECT WEEK("1998-02-20"); -> 7 mysql> SELECT WEEK("1998-02-20",0); -> 7 mysql> SELECT WEEK("1998-02-20",1); -> 8 mysql> SELECT WEEK("1998-12-31",1); -> 53
Note: In version 4.0, the WEEK(#,0) function was changed to match the US calendar.
Note that if the week is the last week of the previous year, MySQL will return 0 unless you specify 2 or 3 as an optional argument:
Mysql> SELECT YEAR("2000-01-01"), WEEK("2000-01-01",0); -> 2000, 0 mysql> SELECT WEEK("2000-01-01",2); -> 52
We can assume that MySQL should return 52 because this date is the 52nd week of the year 1999. We decided to return 0 because we want the function to give "the number of the week in the specified year." This makes the WEEK() function more reliable when used in conjunction with other functions that calculate parts of dates.
If you still care about the correct week of the year, then you can use 2 or 3 as an optional argument, or use YEARWEEK()
Mysql> SELECT YEARWEEK("2000-01-01"); -> 199952 mysql> SELECT MID(YEARWEEK("2000-01-01"),5,2); -> 52
Returns the year for date in the range 1000 to 9999:
Mysql> SELECT YEAR("98-02-03"); -> 1998
YEARWEEK(date) , YEARWEEK(date,first)
Returns the year and week for the date argument. The second argument to this function works similar to the second argument to the WEEK() function. Note that the year may differ from the date specified for the first and last weeks of the year:
Mysql> SELECT YEARWEEK("1987-01-01"); -> 198653
Note that the week number is different from what WEEK() (0) returns when called with the optional argument 0 or 1 . This is because WEEK() returns the week number in the specified year.
Returns the hour for the time argument, ranging from 0 to 23:
Mysql> SELECT HOUR("10:05:03"); -> 10
Returns the number of minutes for the time argument, ranging from 0 to 59:
Mysql> SELECT MINUTE("98-02-03 10:05:03"); -> 5
Returns the number of seconds for the time argument, ranging from 0 to 59:
Mysql> SELECT SECOND("10:05:03"); -> 3
Adds N months to period P (in YYMM or YYYYMM format). Returns a value in YYYYMM format. Note that the period argument P is not a date value:
Mysql> SELECT PERIOD_ADD(9801,2); -> 199803
PERIOD_DIFF(P1,P2)
Returns the number of months between periods P1 and P2. P1 and P2 must be in YYMM or YYYYMM format. Note that the period arguments P1 and P2 are not date values:
Mysql> SELECT PERIOD_DIFF(9802,199703); -> 11
DATE_ADD(date,INTERVAL expr type) , DATE_SUB(date,INTERVAL expr type) , ADDDATE(date,INTERVAL expr type) , SUBDATE(date,INTERVAL expr type)
These functions perform arithmetic operations on dates. Both are new in MySQL 3.22. The ADDDATE() and SUBDATE() functions are synonyms for DATE_ADD() and DATE_SUB() . In MySQL 3.23, you can use the + and - operators instead of the DATE_ADD() and DATE_SUB() functions if the right-hand expression is a column of type DATE or DATETIME (see example below). The date argument is a DATETIME or DATE value that specifies the start date.
The expr expression specifies the amount of interval to add to or subtract from the start date. The expr expression is a string that can begin with - for negative interval values. The type keyword indicates how a given expression should be interpreted. The EXTRACT(type FROM date) helper function returns an interval of the specified type (type) from a date value. The following table shows the relationship between the type and expr arguments:
| Meaning Type | Expected Format expr |
| SECOND | SECONDS |
| MINUTE | MINUTES |
| HOUR | HOURS |
| DAY | DAYS |
| MONTH | MONTHS |
| YEAR | YEARS |
| MINUTE_SECOND | "MINUTES:SECONDS" |
| HOUR_MINUTE | "HOURS:MINUTES" |
| DAY_HOUR | "DAYS HOURS" |
| YEAR_MONTH | "YEARS-MONTHS" |
| HOUR_SECOND | "HOURS:MINUTES:SECONDS" |
| DAY_MINUTE | "DAYS HOURS:MINUTES" |
| DAY_SECOND | "DAYS HOURS:MINUTES:SECONDS" |
In MySQL, the expr expression format allows any delimiters. The delimiters presented in this table are provided as examples. If date is a DATE value and the intended calculation involves only the YEAR , MONTH , and DAY parts (that is, no TIME part), then the result is represented by a DATE value. In other cases, the result is a DATETIME value:
Mysql> SELECT "1997-12-31 23:59:59" + INTERVAL 1 SECOND; -> 1998-01-01 00:00:00 mysql> SELECT INTERVAL 1 DAY + "1997-12-31"; -> 1998-01-01 mysql> SELECT "1998-01-01" - INTERVAL 1 SECOND; -> 1997-12-31 23:59:59 mysql> SELECT DATE_ADD("1997-12-31 23:59:59", INTERVAL 1 SECOND); -> 1998-01-01 00:00:00 mysql> SELECT DATE_ADD("1997-12-31 23:59:59", INTERVAL 1 DAY); -> 1998-01-01 23:59:59 mysql> SELECT DATE_ADD("1997-12-31 23:59:59", INTERVAL "1:1" MINUTE_SECOND); -> 1998-01-01 00:01:00 mysql> SELECT DATE_SUB("1998-01-01 00:00:00", INTERVAL "1 1:1:1" DAY_SECOND); -> 1997-12-30 22:58:59 mysql> SELECT DATE_ADD("1998-01-01 00:00:00", INTERVAL "-1 10" DAY_HOUR); -> 1997-12-30 14:00:00 mysql> SELECT DATE_SUB("1998-01-02", INTERVAL 31 DAY); -> 1997-12-02
If the specified interval is too short (that is, does not include all the parts of the interval expected by the specified type keyword), then MySQL assumes that the leftmost parts of the interval are omitted. For example, if the type argument is DAY_SECOND , then the expected expr expression must have the following parts: days, hours, minutes, and seconds. If you specify the interval value as "1:10" in this case, MySQL assumes that days and hours are omitted and that the value only includes minutes and seconds. In other words, the combination "1:10" DAY_SECOND is interpreted as equivalent to "1:10" MINUTE_SECOND . MySQL interprets TIME values in a similar way - as representing the elapsed time rather than the time of day. Please note that when adding or subtracting operations involving a DATE value and an expression containing a time part, this DATE value will be automatically converted to a DATETIME type value:
Mysql> SELECT DATE_ADD("1999-01-01", INTERVAL 1 DAY); -> 1999-01-02 mysql> SELECT DATE_ADD("1999-01-01", INTERVAL 1 HOUR); -> 1999-01-01 01:00:00
If you use incorrect date values, the result will be NULL. If, when summing MONTH , YEAR_MONTH or YEAR, the day number in the resulting date exceeds the maximum number of days in the new month, then the day number of the resulting date is set to the last day of the new month:
Mysql> SELECT DATE_ADD("1998-01-30", INTERVAL 1 MONTH); -> 1998-02-28
From the previous example, you can see that the INTERVAL word and the type keyword are not case sensitive.
EXTRACT(type FROM date)
The EXTRACT() function uses the same interval types as the DATE_ADD() or DATE_SUB() functions, but EXTRACT() extracts a portion of a date value rather than performing arithmetic.
Mysql> SELECT EXTRACT(YEAR FROM "1999-07-02"); -> 1999 mysql> SELECT EXTRACT(YEAR_MONTH FROM "1999-07-02 01:02:03"); -> 199907 mysql> SELECT EXTRACT(DAY_MINUTE FROM "1999-07-02 01:02:03"); -> 20102
the function returns the day number for the date specified in the date argument, (the number of days that have passed since year 0):
Mysql> SELECT TO_DAYS(950501); -> 728779 mysql> SELECT TO_DAYS("1997-10-07"); -> 729669
The TO_DAYS() function is not intended for use with values prior to the introduction of the Gregorian calendar (1582) because it does not take into account days lost when the calendar was changed.
Returns the DATE value for the given day number N:
Mysql> SELECT FROM_DAYS(729669); -> "1997-10-07"
The FROM_DAYS() function is not intended for use with values prior to the introduction of the Gregorian calendar (1582) because it does not account for days lost when the calendar was changed.
DATE_FORMAT(date,format)
Formats the date value according to the format string. The following qualifiers can be used in the format line:
| Determinant | Description |
| %M | Name of the month (January...December) |
| %W | Name of the day of the week (Sunday...Saturday) |
| %D | Day of the month with English suffix (0st, 1st, 2nd, 3rd, etc.) |
| %Y | Year, date, 4 digits |
| %y | Year, date, 2 digits |
| %X | Year for a week where Sunday is considered the first day of the week, number, 4 digits, used with "%V" |
| %x | Year for a week where Sunday is considered the first day of the week, number, 4 digits, used with "%v" |
| %a | Abbreviated name of the day of the week (Sun...Sat) |
| %d | Day of the month, number (00..31) |
| %e | Day of the month, number (0..31) |
| %m | Month, day (00..12) |
| %c | Month, date (0..12) |
| %b | Abbreviated name of the month (Jan...Dec) |
| %j | Day of the year (001..366) |
| %H | Hour (00..23) |
| %k | Hour (0..23) |
| %h | Hour (01..12) |
| %I | Hour (01..12) |
| %l | Hour (1..12) |
| %i | Minutes, number (00..59) |
| %r | Time, 12-hour format (hh:mm:ss M) |
| %T | Time, 24-hour format (hh:mm:ss) |
| %S | Seconds (00..59) |
| %s | Seconds (00..59) |
| %p | AM or PM |
| %w | Day of the week (0=Sunday..6=Saturday) |
| %U | Week (00..53), where Sunday is considered the first day of the week |
| %u | Week (00..53), where Monday is considered the first day of the week |
| %V | Week (01..53), where Sunday is considered the first day of the week. Used with "%X" |
| %v | Week (01..53), where Monday is considered the first day of the week. Used with " %x " |
| %% | Literal "%". |
All other characters are simply copied into the resulting expression without interpretation:
Mysql> SELECT DATE_FORMAT("1997-10-04 22:23:00", "%W %M %Y"); -> "Saturday October 1997" mysql> SELECT DATE_FORMAT("1997-10-04 22:23:00", "%H:%i:%s"); -> "22:23:00" mysql> SELECT DATE_FORMAT("1997-10-04 22:23:00", "%D %y %a %d %m %b %j"); -> "4th 97 Sat 04 10 Oct 277" mysql> SELECT DATE_FORMAT("1997-10-04 22:23:00", "%H %k %I %r %T %S %w"); -> "22 22 10 10:23:00 PM 22:23:00 00 6" mysql> SELECT DATE_FORMAT("1999-01-01", "%X %V"); -> "1998 52"
In MySQL 3.23, the "%" character must precede format specifier characters. In earlier versions of MySQL, the "%" character is optional.
The reason that the month and day intervals start at zero is that MySQL allows partial dates such as "2004-00-00" since MySQL 3.23.
TIME_FORMAT(time,format)
This function is used similarly to the DATE_FORMAT() function described above, but the format string can only contain format specifiers that relate to hours, minutes, and seconds. When specifying other qualifiers, the value NULL or 0 will be returned.
CURDATE() , CURRENT_DATE
Returns today's date as a value in the format YYYY-MM-DD or YYYYMMDD, depending on whether the function is used in a string or numeric context:
Mysql> SELECT CURDATE(); -> "1997-12-15" mysql> SELECT CURDATE() + 0; -> 19971215
CURTIME() , CURRENT_TIME
Returns the current time as a value in the format HH:MM:SS or HHMMS, depending on whether the function is used in a string or numeric context:
Mysql> SELECT CURTIME(); -> "23:50:26" mysql> SELECT CURTIME() + 0; -> 235026
NOW() , SYSDATE() , CURRENT_TIMESTAMP
Returns the current date and time as a value in the format YYYY-MM-DD HH:MM:SS or YYYYMMDDHHMMSS , depending on whether the function is used in a string or numeric context:
Mysql> SELECT NOW(); -> "1997-12-15 23:50:26" mysql> SELECT NOW() + 0; -> 19971215235026
Note that NOW() is evaluated only once for each request, namely at the beginning of its execution. This ensures that multiple references to NOW() within the same query will produce the same value.
UNIX_TIMESTAMP() , UNIX_TIMESTAMP(date)
When called without an argument, this function returns the UNIX_TIMESTAMP timestamp (seconds since 1970-01-01 00:00:00 GMT) as an unsigned integer. If the UNIX_TIMESTAMP() function is called with a date argument, it returns the value of the argument as the number of seconds since 1970-01-01 00:00:00 GMT. The date argument can be a DATE string, a DATETIME string, a TIMESTAMP value, or a number in the local time format YYMMDD or YYYYMMDD:
Mysql> SELECT UNIX_TIMESTAMP(); -> 882226357 mysql> SELECT UNIX_TIMESTAMP("1997-10-04 22:23:00"); -> 875996580
When using the UNIX_TIMESTAMP function on a TIMESTAMP column, this function will return the value of the internal timestamp directly, without the implied conversion of the string to a timestamp (``string-to-unix-timestamp''). If the given date is outside the valid range, then the UNIX_TIMESTAMP() function will return 0 , but note that only a basic check is performed (year 1970-2037, month 01-12, day 01-31). If you need to perform UNIX_TIMESTAMP() column subtraction, the result can be converted to signed integers. See Section 6.3.5, “Type Casting Functions”.
FROM_UNIXTIME(unix_timestamp)
Returns a representation of the unix_timestamp argument as a value in the format YYYY-MM-DD HH:MM:SS or YYYYMMDDHHMMSS , depending on whether the function is used in a string or numeric context:
Mysql> SELECT FROM_UNIXTIME(875996580); -> "1997-10-04 22:23:00" mysql> SELECT FROM_UNIXTIME(875996580) + 0; -> 19971004222300
FROM_UNIXTIME(unix_timestamp,format)
Returns a string representation of the unix_timestamp argument, formatted according to the format string. The format string can contain the same qualifiers that are listed in the description for the DATE_FORMAT() function:
Mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(), "%Y %D %M %h:%i:%s %x"); -> "1997 23rd December 03:43:30 1997"
SEC_TO_TIME(seconds)
Returns the seconds argument, converted to hours, minutes, and seconds, as a value in the format HH:MM:SS or HHMMSS , depending on whether the function is used in a string or numeric context:
Mysql> SELECT SEC_TO_TIME(2378); -> "00:39:38" mysql> SELECT SEC_TO_TIME(2378) + 0; -> 3938
TIME_TO_SEC(time)
Returns the time argument converted to seconds:
Mysql> SELECT TIME_TO_SEC("22:23:00"); -> 80580 mysql> SELECT TIME_TO_SEC("00:39:38"); -> 2378
publication of this article is permitted only with a link to the website of the author of the article
As you know, all dates are stored in mysql in reverse order year-month-day (2008-10-18), sometimes even without a separator (20081018).
To display the date, you need to convert it into a normal readable form.
There are two methods of conversion, effective and not very effective.
An ineffective way is when the date output from mysql is converted from using php.
I personally did this myself for a very long time. Before displaying, I reversed each date using a php function.
If the number of transformations is not large, then you can reverse the date using PHP, there is nothing wrong with that, but if you need to pull out tens or hundreds of thousands of records and convert the date in each, then of course converting dates using mysql will be much faster.
There is a great function in mysql called DATE_FORMAT(), it is very similar to php function date().
Here is an example of use
SELECT DATE_FORMAT("2008-11-19","%d.%m.%Y");
result
Everything is very simple and fast, there is no need to change dates using php.
Here is a list of definitions for this function
| Determinant | Description |
| %M | Name of the month (January...December) |
| %W | Name of the day of the week (Sunday...Saturday) |
| %D | Day of the month with English suffix (0st, 1st, 2nd, 3rd, etc.) |
| %Y | Year, date, 4 digits |
| %y | Year, date, 2 digits |
| %X | Year for a week where Sunday is considered the first day of the week, number, 4 digits, used with "%V" |
| %x | Year for a week where Sunday is considered the first day of the week, number, 4 digits, used with "%v" |
| %a | Abbreviated name of the day of the week (Sun...Sat) |
| %d | Day of the month, number (00..31) |
| %e | Day of the month, number (0..31) |
| %m | Month, day (00..12) |
| %c | Month, date (0..12) |
| %b | Abbreviated name of the month (Jan...Dec) |
| %j | Day of the year (001..366) |
| %H | Hour (00..23) |
| %k | Hour (0..23) |
| %h | Hour (01..12) |
| %I | Hour (01..12) |
| %l | Hour (1..12) |
| %i | Minutes, number (00..59) |
| %r | Time, 12-hour format (hh:mm:ss M) |
| %T | Time, 24-hour format (hh:mm:ss) |
| %S | Seconds (00..59) |
| %s | Seconds (00..59) |
| %p | AM or PM |
| %w | Day of the week (0=Sunday..6=Saturday) |
| %U | Week (00..53), where Sunday is considered the first day of the week |
| %u | Week (00..53), where Monday is considered the first day of the week |
| %V | Week (01..53), where Sunday is considered the first day of the week. Used with `%X" |
| %v | Week (01..53), where Monday is considered the first day of the week. Used with `%x" |
| %% | Literal `%". |
Comments
27.11.2008 ----
Hey shaitan!!!
I myself have been working on php and mysql for five years, and all the time I was changing the date in php...
It didn’t even occur to me that it would be easier to use the built-in mysql function
11/28/2008 Zheka
Likewise! I've always used my php function
12/03/2008 Sergey
Well, in general, does anyone even use this approach?
Or does everyone use php to reverse the date?
I personally have never reversed a date in mysql, I still do everything in php
06/28/2009 Ilya
Unfortunately, nothing worked :(
07/08/2009 Vitaly
Cool, thanks for the feature. I wonder if there are any other squiggles?
07/14/2009 DSaint
Thank you, it helped a lot. All that remains is to display the name of the month in Russian)
07/28/2009 Vlad
mysql=>PHP
select unix_timestamp(start_date) as start_date_php
php-code
date("d.m.Y",$row["start_date_php"])
PHP=>MySQL
update table set start_date=DATE_FORMAT(STR_TO_DATE("12/19/2009 6:35:22 PM","%d.%m.%Y %H:%i"),"%Y.%m.%d %H:% i")
08/18/2009 Guest
2:DSaint
There is such a wonderful function:
ELT(MONTH("2004-04-10"), "Jan.","Feb.","March","Apr.","May","June","July","Aug."," Sep.","Oct.","Nov.","Dec.")
Use it. :-)
10/29/2009 Vladimir
Thank you, about ELT(MONTH("2004-04-10"), "Jan.","Feb.","March","Apr.","May","June","July","Aug. ","Sep.","Oct.","Nov.","Dec.")
I haven't heard.
10/07/2010 Evgeniy
What works faster? Conversion in the request, or as a result of the PHP function?
10/07/2010 defender
At a minimum, there is less memory to process, fewer function calls, fewer memory allocations... I use something like this all the time, but not in mysql, but in postgresql.
10/08/2010 Admin
Evgeny, defender said correctly, this transformation should work more beautifully through the database, but of course if we're talking about about extracting huge amounts of data.
If you pull 10-20 records, then there is no difference how to convert the date; compared to other loads, this is a trifle.
01/27/2011 pcemma
uhh thanks to the afftor (: there is no longer a need to use my mega cool function for conversion (:
04/13/2011 Xes
HOW TO USE IT IN PHP
while ($sqlr=mysql_fetch_array($sql))
{
echo ($sqlr["comadd"]." ".$sqlr["comment"]."
");
$sqlr["comadd"] - Do you need to present it in normal form?
}
04/14/2011 Vitaly
I have a date in the database in the format 11/19/2008, table type VARCHAR, how can I rewrite it in the database in the format 2008-11-19?
Hands just for a very long time...
Thank you.
04/15/2011 admin
Xes is a MySQL function, you need to use it in an sql query, which, judging by your code, is located somewhere above. It cannot be used in this section of code.
04/15/2011 admin
Vitaly, just change the cell type to DATE, mysql will automatically convert all the data in this cell to the 2008-11-19 format.
But just in case, before changing the cell type, make a dump of this table, because suddenly the database will do something wrong and the table will break altogether.
If it is important for you to leave the field type as varchar, then after you set the DATE type, set it back to varchar...
This is the simplest option, but not entirely correct, but I checked it works.
05/14/2011 DDD
date("d-m-Y",strtotime("$myrow"));
05/24/2011 Konstantin
and I always take SELECT *,UNIX_TIMESTAMP(created) AS created FROM...
But with a motor I can do it in any format. Even just a day, even just a time...
and compare which is greater than 05/14/2011 or 05/14/2010...
And I write it like this:
...date=".gmdate("Y-m-d H:i:s",$created)...
and in general, I see no reason to change habits
05/24/2011 Sergey
Konstantin, yes, I myself use it for php output date(), we’re just looking at the option of converting the date not through PHP, but through mysql.
I would say that this is just an overview of the mysql function and it certainly has a right to exist...
the function
YEARMONTH() is blatantly missing!!! Say you have
two dates and you want to do a period_diff...you
can"t just concat() YEAR() and MONTH() because
month is not "0" padded...just seems with
YEARWEEK() it would make sense to also have
YEARMONTH()...thoughts? I know you can do it with
DATE_FORMAT, but then why all the others
functions?