SQL - Revision history

Hdridder: /* Date and time functions */

2025-05-16T18:55:19Z

Date and time functions

@@ Line 40: / Line 40: @@
   order by date desc
 </syntaxhighlight>
 ;UNIX_TIMESTAMP(NOW())

2024-06-09T07:17:44Z

← Older revision		Revision as of 07:17, 9 June 2024
Line 35:		Line 35:
	:Convert from UTC to local timezone. Look [[MariaDB/MySQL#Populate_and_update_timezone_information\|here]] to set up timezones properly.		:Convert from UTC to local timezone. Look [[MariaDB/MySQL#Populate_and_update_timezone_information\|here]] to set up timezones properly.

	;Sort on a part of a datetime (e.g. to rule out the seconds )		;Sort on a part of a datetime in descending order(e.g. to rule out the seconds )
	<syntaxhighlight lang='sql'>		<syntaxhighlight lang='sql'>
	select date_format(datefield,"%Y-%m-%d %H:%i") as date		select date_format(datefield,"%Y-%m-%d %H:%i") as date
	order by date		order by date desc
	</syntaxhighlight>		</syntaxhighlight>

2024-01-07T20:31:46Z

Basic statements

@@ Line 138: / Line 138: @@
     update column1 = value1,
           column2 = value2
 </syntaxhighlight>

2024-01-03T20:33:20Z

Basic statements

2023-02-15T09:30:20Z

Troubleshooting

← Older revision		Revision as of 09:30, 15 February 2023
Line 133:		Line 133:
	==Troubleshooting==		==Troubleshooting==
	For problems with processes waiting for a lock check [https://stackoverflow.com/questions/19801139/mysql-permanently-getting-waiting-for-table-metadata-lock this]. (item to be updated further)		For problems with processes waiting for a lock check [https://stackoverflow.com/questions/19801139/mysql-permanently-getting-waiting-for-table-metadata-lock this]. (item to be updated further)

			;show processlist;
			:Show all running queries

	=SQLite=		=SQLite=

2021-07-29T09:24:35Z

Like/Regexp/rlike

← Older revision		Revision as of 09:24, 29 July 2021
Line 97:		Line 97:
	;select * from table where column like '%xxx_'		;select * from table where column like '%xxx_'
	:Match all records where column1 values have at least 5 characters and end with xxx or XXX and an arbitrary single character		:Match all records where column1 values have at least 5 characters and end with xxx or XXX and an arbitrary single character
	~~With~~ like:
			Adding 'binary' in front of the expression makes the match case sensitive.

			Using like:
	;%		;%
	:Match 1 or more characters		:Match 1 or more characters
	;_		;_
	:Match exactly 1 character		:Match exactly 1 character
	~~With regexp or rlike you can use POSIX [[Regular Expressions]]~~

	~~Adding~~ '~~binary~~' in front ~~of the expression makes~~ the ~~match case sensitive~~.		* Putting '\' in front escapes these wildcards (the % or _ character is matched).
	;select * from table where column1 regexp binary '.+~~xxx_~~'		With <code>regexp</code> or <code>rlike</code> you can use POSIX [[Regular Expressions]]

			;select * from table where column1 regexp binary '.+xxx.$'
	:Match all records where column1 values have at least 5 characters and end with xxx and an arbitrary single character		:Match all records where column1 values have at least 5 characters and end with xxx and an arbitrary single character

	==Basic statements==		==Basic statements==
	<syntaxhighlight lang=sql>		<syntaxhighlight lang=sql>

2021-07-27T12:36:31Z

Date and time functions

← Older revision		Revision as of 12:36, 27 July 2021
Line 19:		Line 19:

	==Date and time functions==		==Date and time functions==
			;NOTE!!
			:To avoid conversion that depend on the timezone of your client or server use the unix_time (seconds since 1-1-1970) as integer whenever possible. Also in programs avoid date/time conversions except for presentation.

	;NOW()		;NOW()
	;UTC_TIMESTAMP()		;UTC_TIMESTAMP()

2021-06-29T09:17:37Z

Basic statements

← Older revision		Revision as of 09:17, 29 June 2021
Line 122:		Line 122:
	where column1 = value;		where column1 = value;
	</syntaxhighlight>		</syntaxhighlight>

			==Troubleshooting==
			For problems with processes waiting for a lock check [https://stackoverflow.com/questions/19801139/mysql-permanently-getting-waiting-for-table-metadata-lock this]. (item to be updated further)

	=SQLite=		=SQLite=

2021-06-03T10:50:02Z

@@ Line 90: / Line 90: @@
 Combine data from more tables. [https://www.w3schools.com/sql/sql_join.asp w3schools] have a nice page on this.
 ==Basic statements==
 <syntaxhighlight lang=sql>

2021-04-15T10:19:02Z

Date and time functions

← Older revision		Revision as of 10:19, 15 April 2021
Line 29:		Line 29:
	:Get the date part from a time		:Get the date part from a time

	;convert_tz(date,"UTC",~~"MET"~~)		;convert_tz(date,"UTC",@@session.time_zone)
	:Convert from UTC to local timezone. Look [[MariaDB/MySQL#Populate_and_update_timezone_information\|here]] to set up timezones properly.		:Convert from UTC to local timezone. Look [[MariaDB/MySQL#Populate_and_update_timezone_information\|here]] to set up timezones properly.