• notice
  • Congratulations on the launch of the Sought Tech site

MYSQL gets the current date and formats

MySQL gets the current date and date format

Get the system date: NOW()

Format the date: DATE_FORMAT(date, format)

Note: date: time field

format: date format

Return the system date, output 2009-12-25 14:38:59

select now();

Output 09-12-25

select date_format(now(),'%y-%m-%d');

Format the date value according to the format string:

  • %S, %s two-digit seconds (00,01,..., 59)
  • %I, %i two-digit minutes (00,01,..., 59)
  • %H Two-digit hour, 24 hours (00,01,..., 23)
  • %h Two-digit hour, 12 hours (01,02,..., 12)
  • %k hour as a number, 24 hours (0,1,..., 23)
  • %l hour as a number, 12 hours (1, 2,..., 12)
  • %T 24-hour time format (hh:mm:ss)
  • %r 12-hour time format (hh:mm:ss AM or hh:mm:ss PM)
  • %p AM or PM
  • %W The name of each day of the week (Sunday, Monday,..., Saturday)
  • %a is the abbreviation of the name of each day of the week (Sun, Mon,..., Sat)
  • %d two digits indicate the day of the month (00, 01,..., 31)
  • %e represents the number of days in the month (1, 2,..., 31) in numeric form
  • %D English suffix indicates the number of days in the month (1st, 2nd, 3rd,...)
  • %w represents the number of days of the week as a number (0 = Sunday, 1=Monday,..., 6=Saturday)
  • %j uses three digits to indicate the number of days in the year (001, 002,..., 366)
  • %U week (0, 1, 52), where Sunday is the first day of the week
  • %u week (0, 1, 52), where Monday is the first day of the week
  • %M month name (January, February,..., December)
  • %b abbreviated month name (January, February,..., December)
  • %m two-digit month (01, 02,..., 12)
  • %c number of months (1, 2,...., 12)
  • %Y year represented by four digits
  • %y year represented by two digits
  • %% direct value "%"

MySQL get the current date and time function

1.1 Get the current date + time (date + time) function: now()

mysql> select now();

+---------------------+
| now() |
+---------------------+
| 2008-08-08 22:20:46 |
+---------------------+

In addition to the now() function that can get the current date and time, there are the following functions in MySQL:

  1. current_timestamp()
  2. ,current_timestamp
  3. ,localtime()
  4. ,localtime
  5. ,localtimestamp - (v4.0.6)
  6. ,localtimestamp() - (v4.0.6)

These date and time functions are equivalent to now().Since the now() function is short and easy to remember, it is recommended to always use now() instead of the functions listed above.

1.2 Get the current date + time (date + time) function: sysdate()

sysdate() The date and time function is similar to now(), the difference is: now() gets the value at the beginning of execution, and sysdate() gets the value dynamically when the function is executed.Look at the following example to understand:

mysql> select now(), sleep(3), now();

+---------------------+----------+---------------- -----+
| now() | sleep(3) | now() |
+---------------------+----------+---------------- -----+
| 2008-08-08 22:28:21 | 0 | 2008-08-08 22:28:21 |
+---------------------+----------+---------------- -----+mysql> select sysdate(), sleep(3), sysdate();

+---------------------+----------+---------------- -----+
| sysdate() | sleep(3) | sysdate() |
+---------------------+----------+---------------- -----+
| 2008-08-08 22:28:41 | 0 | 2008-08-08 22:28:44 |
+---------------------+----------+---------------- -----+

It can be seen that although we sleep for 3 seconds in the middle, the time value of the now() function is the same twice; the time value obtained by the sysdate() function differs by 3 seconds.MySQL Manual describes sysdate() like this: Return the time at which the functionexecutes.

sysdate() The date and time function is rarely used in general.

2.Get the current date (date) function: curdate()

mysql> select curdate();

+------------+
| curdate() |
+------------+
| 2008-08-08 |
+------------+

Among them, the following two date functions are equivalent to curdate():

current_date()

,current_date

3.Get the current time (time) function: curtime()

mysql> select curtime();

+-----------+
| curtime() |
+-----------+
| 22:41:30 |
+-----------+

Among them, the following two time functions are equivalent to curtime():

current_time()

,current_time

4.Get the current UTC date and time functions: utc_date(), utc_time(), utc_timestamp()

mysql> select utc_timestamp(), utc_date(), utc_time(), now()

+---------------------+------------+------------+- --------------------+
| utc_timestamp() | utc_date() | utc_time() | now() |
+---------------------+------------+------------+- --------------------+
| 2008-08-08 14:47:11 | 2008-08-08 | 14:47:11 | 2008-08-08 22:47:11 |
+---------------------+------------+------------+- --------------------+

Because our country is located in the East Eight Time Zone, local time = UTC time + 8 hours.UTC time is very useful when the business involves multiple countries and regions.

Second, MySQL date and time Extract (select) function.

1.Select each part of the date and time: date, time, year, quarter, month, day, hour, minute, second, microsecond

set @dt = '2008-09-10 07:15:30.123456';

select date(@dt); - 2008-09-10
select time(@dt); - 07:15:30.123456
select year(@dt); - 2008
select quarter(@dt); - 3
select month(@dt); - 9
select week(@dt); - 36
select day(@dt); - 10
select hour(@dt); - 7
select minute(@dt); - 15
select second(@dt); - 30
select microsecond(@dt); - 123456

2.MThe ySQL Extract() function can achieve similar functions above:

set @dt = '2008-09-10 07:15:30.123456';

select extract(year from @dt); - 2008
select extract(quarter from @dt); - 3
select extract(month from @dt); - 9
select extract(week from @dt); - 36
select extract(day from @dt); - 10
select extract(hour from @dt); - 7
select extract(minute from @dt); - 15
select extract(second from @dt); - 30
select extract(microsecond from @dt); - 123456select extract(year_month from @dt); - 200809
select extract(day_hour from @dt); - 1007
select extract(day_minute from @dt); - 100715
select extract(day_second from @dt); - 10071530
select extract(day_microsecond from @dt); - 10071530123456
select extract(hour_minute from @dt); - 715
select extract(hour_second from @dt); - 71530
select extract(hour_microsecond from @dt); - 71530123456
select extract(minute_second from @dt); - 1530
select extract(minute_microsecond from @dt); - 1530123456
select extract(second_microsecond from @dt); - 30123456

The MySQLExtract() function should have all functions except date() and time().And it also has functions such as selecting ‘day_microsecond’.Note that instead of selecting only day and microsecond, it selects from the day part of the date to the microsecond part.Strong enough!

The only downside of the MySQL Extract() function is that you need to hit the keyboard several times.

3.MySQL dayof… functions: dayofweek(), dayofmonth(), dayofyear()

Respectively return the date parameters, the position in the week, month, and year.

set @dt = '2008-08-08';

select dayofweek(@dt); - 6
select dayofmonth(@dt); - 8
select dayofyear(@dt); - 221

The date ‘2008-08-08’ is the 6th day of the week (1 = Sunday, 2 = Monday, …, 7 = Saturday); the 8th day of the month; the 221st day of the year.

4.MySQL week...functions: week(), weekofyear(), dayofweek(), weekday(), yearweek()

set @dt = '2008-08-08';

select week(@dt); - 31
select week(@dt,3); - 32
select weekofyear(@dt); - 32

select dayofweek(@dt); - 6
select weekday(@dt); - 4

select yearweek(@dt); - 200831

The MySQL week() function can have two parameters, see the manual for details.weekofyear(), like week(), calculates the number of weeks in the year that "a certain day" is located.weekofyear(@dt) is equivalent to week(@dt,3).

The MySQLweekday() function is similar to dayofweek() in that it returns the position of "a certain day" in the week.The difference lies in the reference standard, weekday: (0 =Monday, 1 = Tuesday, …, 6 = Sunday); dayofweek: (1 = Sunday, 2 = Monday,…, 7 = Saturday)

MySQL yearweek() function, returns year(2008) + week position (31).

5.MySQL returns week and month name functions: dayname(), monthname()

set @dt = '2008-08-08';

select dayname(@dt); - Friday
select monthname(@dt); - August

Think about how to return the Chinese name?

6.MySQL last_day() function: Returns the last day of the month.

select last_day('2008-02-01'); - 2008-02-29
select last_day('2008-08-08'); - 2008-08-31

The MySQL last_day() function is very useful.For example, if I want to get the number of days in the current month, I can calculate it like this:

mysql> select now(), day(last_day(now())) as days;

+---------------------+------+
| now() | days |
+---------------------+------+
| 2008-08-09 11:45:45 | 31 |
+---------------------+------+

3.MySQL date and time calculation functions

1.MySQL adds a time interval to the date: date_add()

set @dt = now();

select date_add(@dt, interval 1 day); - add 1 day
select date_add(@dt, interval 1 hour); - add 1 hour
select date_add(@dt, interval 1 minute); -...
select date_add(@dt, interval 1 second);
select date_add(@dt, interval 1 microsecond);
select date_add(@dt, interval 1 week);
select date_add(@dt, interval 1 month);
select date_add(@dt, interval 1 quarter);
select date_add(@dt, interval 1 year);select date_add(@dt, interval -1 day); - sub 1 day

MySQL adddate(), addtime() functions can be replaced by date_add().The following is an example of date_add() implementing addtime() function:

mysql> set @dt = '2008-08-09 12:12:33';

mysql>
mysql> select date_add(@dt, interval '01:15:30' hour_second);

+------------------------------------------------+
| date_add(@dt, interval '01:15:30' hour_second) |
+------------------------------------------------+
| 2008-08-09 13:28:03 |
+------------------------------------------------+ mysql> select date_add(@dt, interval '1 01:15:30' day_second);

+------------------------------------------------- +
| date_add(@dt, interval '1 01:15:30' day_second) |
+------------------------------------------------- +
| 2008-08-10 13:28:03 |
+------------------------------------------------- +

The date_add() function adds "1 hour, 15 minutes and 30 seconds" and "1 day, 1 hour, 15 minutes and 30 seconds" to @dt respectively.Suggestion: always use date_add() date time function instead of adddate(), addtime().

2.MySQL subtracts a time interval from the date: date_sub()

mysql> select date_sub('1998-01-01 00:00:00', interval '1 1:1:1' day_second);

+------------------------------------------------- ---------------+
| date_sub('1998-01-01 00:00:00', interval '1 1:1:1' day_second) |
+----------------------------------------------------------------+
| 1997-12-30 22:58:59 |
+------------------------------------------------- ---------------+

The MySQL date_sub() date and time function has the same usage as date_add(), so I won’t repeat it.In addition, there are two functions in MySQL, subdate(), subtime().It is recommended to use date_sub() instead.

3.MySQL alternative date functions: period_add(P,N), period_diff(P1,P2)

The format of the function parameter "P" is "YYYYMM" or "YYMM", and the second parameter "N" means adding or subtracting N month (month).

MySQL period_add(P,N): Date plus/minus N months.

mysql> select period_add(200808,2), period_add(20080808,-2)

+----------------------+-------------------------+
| period_add(200808,2) | period_add(20080808,-2) |
+----------------------+-------------------------+
| 200810 | 20080806 |
+----------------------+-------------------------+
MySQL period_diff(P1,P2): Date P1-P2, returns N months.

mysql> select period_diff(200808, 200801);

+-----------------------------+
| period_diff(200808, 200801) |
+-----------------------------+
| 7 |
+-----------------------------+

In MySQL, these two date functions are rarely used in general.

4.MySQL date and time subtraction functions: datediff(date1,date2), timediff(time1,time2)

MySQL datediff(date1,date2): subtract two dates date1-date2, return the number of days.

select datediff('2008-08-08', '2008-08-01'); - 7
select datediff('2008-08-01', '2008-08-08'); - -7

MySQL timediff(time1,time2): Subtract time1-time2 from two dates, and return the time difference.

select timediff('2008-08-08 08:08:08', '2008-08-08 00:00:00'); - 08:08:08
select timediff('08:08:08', '00:00:00'); - 08:08:08

Note: The two parameter types of the timediff(time1,time2) function must be the same.

Four, MySQL date conversion function, time conversion function

1.MySQL (time, second) conversion functions: time_to_sec(time), sec_to_time(seconds)

select time_to_sec('01:00:05'); - 3605
select sec_to_time(3605); - '01:00:05'

2.MySQL (date, number of days) conversion functions: to_days(date), from_days(days)

select to_days('0000-00-00'); - 0
select to_days('2008-08-08'); - 733627 select from_days(0); - '0000-00-00'
select from_days(733627); - '2008-08-08'

3.MySQL Str to Date function: str_to_date(str, format)

select str_to_date('08/09/2008','%m/%d/%Y'); - 2008-08-09
select str_to_date('08/09/08','%m/%d/%y'); - 2008-08-09
select str_to_date('08.09.2008','%m.%d.%Y'); - 2008-08-09
select str_to_date('08:09:30','%h:%i:%s'); - 08:09:30
select str_to_date('08.09.2008 08:09:30','%m.%d.%Y %h:%i:%s'); - 2008-08-09 08:09:30

As you can see, the str_to_date(str,format) conversion function can convert some messy character strings into date format.In addition, it can also be converted to time."Format" can be found in the MySQL manual.

4.MySQL Date/Time to Str (date/time conversion to string) functions: date_format(date,format), time_format(time,format)

mysql> select date_format('2008-08-08 22:23:00','%W %M %Y');

+------------------------------------------------+
| date_format('2008-08-08 22:23:00','%W %M %Y') |
+------------------------------------------------+
| Friday August 2008 |
+------------------------------------------------+ mysql> select date_format('2008-08-08 22:23:01','%Y%m%d%H%i%s');

+------------------------------------------------- ---+
| date_format('2008-08-08 22:23:01','%Y%m%d%H%i%s') |
+------------------------------------------------- ---+
| 20080808222301 |
+------------------------------------------------- ---+mysql> select time_format('22:23:01','%H.%i.%s');

+-------------------------------------+
| time_format('22:23:01','%H.%i.%s') |
+-------------------------------------+
| 22.23.01 |
+-------------------------------------+

MySQL date and time conversion functions: date_format(date,format), time_format(time,format) can convert a date/time into various string formats.It is an inverse conversion of the str_to_date(str,format) function.

5.MySQL obtains the country and region time format function: get_format()

MySQL get_format() syntax:

get_format(date|time|datetime,'eur'|'usa'|'jis'|'iso'|'internal'

All examples of MySQL get_format() usage:

select get_format(date,'usa'); -'%m.%d.%Y'
select get_format(date,'jis'); -'%Y-%m-%d'
select get_format(date,'iso'); -'%Y-%m-%d'
select get_format(date,'eur'); -'%d.%m.%Y'
select get_format(date,'internal'); -'%Y%m%d'
select get_format(datetime,'usa'); -'%Y-%m-%d %H.%i.%s'
select get_format(datetime,'jis'); -'%Y-%m-%d %H:%i:%s'
select get_format(datetime,'iso'); -'%Y-%m-%d %H:%i:%s'
select get_format(datetime,'eur'); -'%Y-%m-%d %H.%i.%s'
select get_format(datetime,'internal'); -'%Y%m%d%H%i%s'
select get_format(time,'usa'); -'%h:%i:%s %p'
select get_format(time,'jis'); -'%H:%i:%s'
select get_format(time,'iso'); -'%H:%i:%s'
select get_format(time,'eur'); -'%H.%i.%s'
select get_format(time,'internal'); -'%H%i%s'

The MySQL get_format() function is rarely used in practice.

6.MySQL piece together date and time functions: makdedate(year,dayofyear), maketime(hour,minute,second)

select makedate(2001,31); - '2001-01-31'
select makedate(2001,32); - '2001-02-01' select maketime(12,15,30); - '12:15:30' 

5.MySQL Timestamp function

1.MySQL obtains the current timestamp function: current_timestamp, current_timestamp()

mysql> select current_timestamp, current_timestamp();

+---------------------+---------------------+
| current_timestamp | current_timestamp() |
+---------------------+---------------------+
| 2008-08-09 23:22:24 | 2008-08-09 23:22:24 |
+---------------------+---------------------+

2.MySQL (Unix timestamp, date) conversion function:

unix_timestamp(),
unix_timestamp(date),
from_unixtime(unix_timestamp),
from_unixtime(unix_timestamp,format)

The following is an example:

select unix_timestamp(); - 1218290027
select unix_timestamp('2008-08-08'); - 1218124800
select unix_timestamp('2008-08-08 12:30:00'); - 1218169800select from_unixtime(1218290027); - '2008-08-09 21:53:47'
select from_unixtime(1218124800); - '2008-08-08 00:00:00'
selectfrom_unixtime(1218169800); - '2008-08-08 12:30:00' selectfrom_unixtime(1218169800,'%Y %D %M %h:%i:%s %x'); - '2008 8th August12: 30:00 2008'

3.MySQL timestamp (timestamp) conversion, increase and decrease functions:

timestamp(date) - date to timestamp
timestamp(dt,time) - dt + time
timestampadd(unit,interval,datetime_expr) -
timestampdiff(unit,datetime_expr1,datetime_expr2) --

Please see the example section:

select timestamp('2008-08-08'); - 2008-08-08 00:00:00
select timestamp('2008-08-08 08:00:00', '01:01:01'); - 2008-08-08 09:01:01
selecttimestamp('2008-08-08 08:00:00', '10 01:01:01'); - 2008-08-1809:01:01select timestampadd(day, 1, '2008-08-08 08: 00:00'); --2008-08-09 08:00:00
select date_add('2008-08-08 08:00:00', interval 1 day); - 2008-08-09 08:00:00

The MySQL timestampadd() function is similar to date_add().

select timestampdiff(year,'2002-05-01','2001-01-01'); - -1
select timestampdiff(day ,'2002-05-01','2001-01-01'); - -485
select timestampdiff(hour,'2008-08-08 12:00:00','2008-08-08 00:00:00'); - -12
select datediff('2008-08-08 12:00:00', '2008-08-01 00:00:00'); - 7

The MySQL timestampdiff() function is much more powerful than the datediff() function.datediff() can only calculate the number of days between two dates (date).

VI.MySQL timezone conversion function convert_tz(dt,from_tz,to_tz)

selectconvert_tz('2008-08-08 12:00:00','+08:00','+00:00'); - 2008-08-0804:00 :00

Time zone conversion can also be achieved through date_add, date_sub, and timestampadd.

select date_add('2008-08-08 12:00:00', interval -8 hour); - 2008-08-08 04:00:00
select date_sub('2008-08-08 12:00:00', interval 8 hour); - 2008-08-08 04:00:00
select timestampadd(hour, -8, '2008-08-08 12:00:00'); - 2008-08-08 04:00:00

Tags

Technical otaku

Sought technology together

Related Topic

1 Comments

author

atorvastatin usa & lt;a href="https://lipiws.top/"& gt;order generic lipitor 20mg& lt;/a& gt; atorvastatin 40mg brand

Inkpic

2024-03-08

Leave a Reply

+