Date/Time Sütun Tipleri - TIMESTAMP WITH TIME ZONE (timestamptz ) - UTC Zamanı Saklar

Giriş
Not : DATE sütun tipine de bakabilirsiniz.

TIMESTAMP WITHOUT TIME ZONE sütun tipine de bakabilirsiniz.

SQL cümlesindeki veya bağlantıdaki saat dilimi bilgisini kullanarak girdiyi UTC saatine çevirir ve saat dilimi bilgisini saklamaz. Yani sadece UTC bilgisini saklar. Açıklaması şöyle.

For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's TimeZone parameter, and is converted to UTC using the offset for the timezone zone.

When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone, and displayed as local time in that zone. To see the time in another time zone, either change timezone or use the AT TIME ZONE construct (see Section 9.9.3).

Default Değer İle Kullanımı

Şöyle yaparız

CREATE TABLE users(
  id SERIAL PRIMARY KEY NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  firstName VARCHAR(50),
  lastName VARCHAR(50),
  registerdAt TIMESTAMPTZ DEFAULT now()
);

Çözünürlük

Mikrosaniye çözünürlük kullanılır. Açıklaması şöyle.

Any date-time value submitted with a time zone or offset-from-UTC is processed by using that zone/offset to determine a value in UTC. That UTC value is then stored in the database, with a resolution of microseconds (not the milliseconds of legacy Java java.util.Date & Calendar classes, and not the nanoseconds of java.time classes). After the UTC adjustment is made, the zone/offset information is discarded. If you care about the original zone/offset, store that in a separate column explicitly.

When retrieved, the date-time value is sent out from Postgres in UTC. An intervening tool such as psql or pgAdmin may confusingly apply a default time zone. While well-intentioned, such a feature creates the illusion of the stored value carrying a time zone when in fact it does not. In contrast, a JDBC driver compliant with JDBC 4.2 and later will handle the UTC and zone automatically for you.

Precision

Açıklaması şöyle. Precision 0 ve 6 arasında bir sayı alabilir.

time, timestamp, and interval accept an optional precision value p which specifies the number of fractional digits retained in the seconds field. By default, there is no explicit bound on precision. The allowed range of p is from 0 to 6.

Precision verilmesinin sebebi sürekli yuvarlama yapılmak istenmemesi. Açıklaması şöyle.

If you need rounded values, storing them rounded is going to be faster and less error-prone than dynamically rounding each time you access it.

And having less precision means more ties so more opportunity for index duplicate compression (on versions new enough to offer that). It would also offer better compression for the datafiles when they are offline (like in backups) and possibly when online if your FS offers built-in compression.

Örnek

Elimizde şöyle bir tablo olsun

CREATE TABLE BOOKMARK.TEMP_TABLE
(
   mindatetime timestamp,
   ...
);

Çıktı olarak şunu alırız. Mikro saniye olduğu için 1 milyona kadardır.

mindatetime          
2019-05-08 08:29:50.0
2019-08-08 20:04:14.994077
2019-08-08 21:40:08.362082
2019-08-08 23:03:04.270083
2019-08-09 00:31:20.487717
2019-08-19 15:08:41.167284
2019-08-09 00:31:20.487717
2019-08-19 15:08:41.167284

Gösterimde Yerel Saate Çevrilir
Örnek

Veri tabanına şöyle bir kayıt ekleyelim.

INSERT INTO messages ( user_id, message, left_at )
VALUES ( 3, 'Howdy!', '2011-09-27 17:17:25' );

America/New_York zaman dilimindeki birisi sorgularsa şunu görür.

user_id | 3
message | Howdy!
left_at | 2011-09-27 20:17:25-04

Europe/Stockholm zaman dilimindeki birisi sorgularsa şunu görür.

user_id | 3
message | Howdy!
left_at | 2011-09-28 02:17:25+02

Java 7 Kullanımı
java.util.Date, java.sql.Timestamp, java.util.GregorianCalendar ve javax.xml.datatype.XMLGregorianCalendar sınıfları ile bu sütun tipi kullanılabilir.

Örnek
Hibernate/JPA ile şöyle yaparız.

@Column(name = "run_from", columnDefinition= "TIMESTAMP WITH TIME ZONE")
@Temporal(TemporalType.TIMESTAMP)
private Date runFrom;

- Hibernate java.util.Date tipindeki alan için veri tabanında DATE tipinde sütun yaratır. Yani sadece tarih bilgisini saklar. @Column anotasyonuna yaptığımız ekleme ile TIMESTAMP WITH TIME ZONE tipinde sütun yaratılır.

- @Temporal.TIMESTAMP ile hem tarih hem de saat bilgisini saklarız.

Java 8 Kullanımı
java.time.Instant, java.time.OffsetDateTime, java.time.ZonedDateTime sınıfları ile bu sütun tipi kullanılabilir.

SELECT WHERE IN

Giriş

Select cümlesinin yapısı şöyle. In cümlesi WhereClause'un bir parçası

SelectClause + FromClause + WhereClause + GroupByClause + OrderByClause + LimitClause

IN cümlesi aslında IN parantezi içindeki bir sürü değer ile OR'lamak gibidir.

Örnek

IN ile şöyle yaparız.

SELECT * FROM [DPPLBMS].[dbo].[STUDENTS_DETAILS]
where AttendingConvocation='0' and grade in ('A', 'B', 'C')

Eğer bunu OR ile yapmak istersek şöyle yaparız

SELECT * FROM [DPPLBMS].[dbo].[STUDENTS_DETAILS]
where AttendingConvocation='0' and (grade = 'A' or grade='B' or Grade='C')

Örnek
Şöyle yaparız.

SELECT * FROM table WHERE 'val' IN (col1,col2);




CREATE OR REPLACE FUNCTION

Giriş

Söz dizimi şöyle

CREATE OR REPLACE FUNCTION function_name(parameter_type parameter_name, ...)
RETURNS return_type AS $$
BEGIN
  -- Function logic
END;
$$ LANGUAGE plpgsql;

1. Scalar Functions

Açıklaması şöyle

Scalar functions are user-defined functions that return a single value. They can take one or more input parameters and execute SQL statements. 

Örnek

Şöyle yaparız.

CREATE FUNCTION myfunction(arg1 INTEGER, arg2 TEXT) RETURNS INTEGER
AS $$
DECLARE result INTEGER;
BEGIN
  SELECT COUNT(*) INTO result FROM mytable WHERE mycolumn = arg2;
  RETURN result * arg1;
END;
$$ LANGUAGE plpgsql;

Örnek

Şöyle yaparız

-- Create a simple function that returns the sum of two integers
CREATE OR REPLACE FUNCTION add_two_numbers(a INT, b INT) RETURNS INT AS $$
BEGIN
  RETURN a + b;
END;
$$ LANGUAGE plpgsql;

-- Call the function
SELECT add_two_numbers(5, 7); -- Returns 12

Açıklaması şöyle

In the above example, we create a function add_two_numbers that takes two integers as input parameters and returns their sum. We use the CREATE FUNCTION statement with the RETURNS keyword to specify the return type.

Örnek

Şöyle yaparız. Verilen parametreleri tabloya ekler

CREATE OR REPLACE FUNCTION MyInsert(balance NUMERIC(19), id VARCHAR(250)) RETURNS VOID
AS
  $BODY$
    BEGIN
      INSERT INTO balance(balance, invoice_items_id) VALUES (balance , id);
    END;
  $BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;

2. Table-Valued Functions

Açıklaması şöyle

Table-valued functions return a set of rows as a result. You can think of them as producing a virtual table.

Örnek

Şöyle yaparız

-- Create a table-valued function that returns all employees in a department
CREATE OR REPLACE FUNCTION get_employees_in_department(dept_id INT) 
RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
  RETURN QUERY SELECT employee_id, employee_name 
  FROM employees WHERE department_id = dept_id;
END;
$$ LANGUAGE plpgsql;

-- Call the function
-- Returns all employees in department 101
SELECT * FROM get_employees_in_department(101); 

Açıklaması şöyle

In this example, we create a function get_employees_in_department that takes a department ID as input and returns a table of employees in that department. We use the RETURNS TABLE clause to specify the return type.

3. Aggregation Functions

Açıklaması şöyle

Aggregation functions are used to perform operations on sets of values and return a single result. PostgreSQL allows you to create custom aggregation functions to suit your specific needs.

Örnek

Şöyle yaparız

-- Create an aggregation function that calculates the median
  
CREATE OR REPLACE FUNCTION median_accumulator(sfunc INTERNAL, stype INTERNAL,
  finalfunc INTERNAL)
RETURNS internal STRICT
LANGUAGE C;

-- Define the final function
CREATE OR REPLACE FUNCTION median_finalfunc(internal) RETURNS double precision
LANGUAGE sql IMMUTABLE STRICT AS $$
  SELECT CASE
    WHEN $1 IS NULL THEN NULL
    ELSE (percentile_cont(0.5) WITHIN GROUP (ORDER BY $1))
  END;
$$;

-- Create an aggregate that uses the function
CREATE AGGREGATE median(double precision) (
  SFUNC = median_accumulator,
  STYPE = internal,
  FINALFUNC = median_finalfunc
);

Açıklaması şöyle

In this example, we create a custom aggregation function for calculating the median of a set of values. Custom aggregations give you the flexibility to extend PostgreSQL’s built-in aggregation functions.