Tag: PostgreSQL

Retrieving age from date of birth in PostgreSQL

select extract(year from age(timestamp '1971-02-23')) as age

select date_part('year', age(timestamp '1971-02-23')) as age
Advertisements

Dynamiczne sortowanie w PostgreSQL

               select 
			mm.userid as UserId, 
			coalesce(mm.firstname,'') || coalesce(' ' || mm.lastname,'') as UserName,
			mm.description as Description, 
			mm.usr as ModifyUser, 
			mm.date as ModifyDateTime
		from ext.rekordy as mm		
		where 
			(_firstname is null or mm.firstname ilike '%' || _firstname || '%') 
			AND (_lastname is null or mm.lastname ilike '%' || _lastname || '%')
			AND (_pesel is null or mm.pesel = _pesel)
			AND (_modifydatetimefrom IS NULL OR mm.date >= _modifydatetimefrom)
			AND (_modifydatetimeto IS NULL OR mm.date <= _modifydatetimeto)
		order by 
			case 
				when _sort_direction = 'DESC' then
					case 
						when _sort_column_name = 'UserId' then cast(mm_log.userid as text)
						when _sort_column_name = 'UserName' then cast(coalesce(mm.firstname,'') || coalesce(' ' || mm.lastname,'') as text)
						when _sort_column_name = 'Description' then cast(mm.description as text)
						when _sort_column_name = 'ModifyUser' then cast(mm.usr as text)
						when _sort_column_name = 'ModifyDateTime' then cast(mm.date as text)
						else cast(mm.userid as text)
					end
			end desc,
			case 
				when _sort_direction = 'ASC' then
					case 
						when _sort_column_name = 'UserId' then cast(mm.userid as text)
						when _sort_column_name = 'UserName' then cast(coalesce(mm.firstname,'') || coalesce(' ' || mm.lastname,'') as text)
						when _sort_column_name = 'Description' then cast(mm.description as text)
						when _sort_column_name = 'ModifyUser' then cast(mm.usr as text)
						when _sort_column_name = 'ModifyDateTime' then cast(mm.date as text)
						else cast(mm.idx_osoby as text)
					end
			end asc			
		offset _skip limit _take