Best way to save date and time in a PostgreSQL database

Question

I'm gonna to save date and time in my PostgreSQL database and fetch and show it to the user in an appropriate format.

Suppose that application users are located in Iran and use Jalali date/time system. In the first half of year, Iran time is UTC+04:30, but in the rest of year, it is UTC+03:30. As a matter of fact daylight saving time is used in Iran.

IMPORTANT: Sometimes we make a decision and change database server location from Iran to Europe or elsewhere.

Now I have some questions:

1. What data type is more convenient to save date with time? TIMESTAMP (TIMESTAMP WITHOUT TIMEZONE) or TIMESTAMPZ (TIMESTAMP WITH TIME ZONE)?

2. What data type is more convenient to save time only? TIME or TIME WITH TIME ZONE?

3. What data type is more convenient to save date only? DATE (Gregorian) String (Jalali) or a custom data type?

4. How can I set TIMEZONE for our database once? I wanna set TIMEZONE for database one time and after that all queries over TIMESTAMPZ columns will be saved and fetched within that TIMEZONE and also it considered daylight saving time ?

5. What SQL query is the best when I wan to save current date and time ?

   a- INSERT INTO test(d) VALUES(now());

   b- INSERT INTO test(d) VALUES(now() at time zone 'utc');

   c- INSERT INTO test(d) VALUES(now() at time zone 'Asia/Tehran');

   d- INSERT INTO test(d) VALUES(current_timestamp);

   e- INSERT INTO test(d) VALUES(now() at time zone 'utc');

   f- INSERT INTO test(d) VALUES(now() at time zone 'Asia/Tehran');

6. Is number 5-c and 5-f considered daylight saving times when save it or not?

7. Is number 5-a and 5-d saved in 'Asia/Tehran' when database has 'Asia/Tehran' time zone ?

8. When I wan to query from database, what options is the best in my situation ?

   a- SELECT d FROM test;

   b- SELECT d at time zone 'utc' FROM test;

   c- SELECT d at time zone 'Asia/Tehran' FROM test;

9. Is number 7-c considered daylight saving times?

10. Is number 7-a considered 'Asia/Tehran' time zone and daylight saving time when database has 'Asia/Tehran' time zone?

If I'd better use the timestamp to save date/time, then I have to add daylight saving time to it and convert it to Jalali date time and show it to the user, and vice versa.

Solution

https://www.postgresql.org/docs/current/static/datatype-datetime.html

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

1. timestamptz
2. timestamptz and select date only
3. timestamptz and select time only
4. database saves tz aware time stamps in UTC, no matter your locale or settings. stamps are always converted to UTC adjusting it by TimeZone parameter. Same deconvert happens on displaying data every time. TimeZone for server is only default value, used if client does not specify any. database TimeZone overrides the one in postgresql.conf for specified database, but still, client settings will override the database ones.

Comments

[Pezhman Parsaee]

Thank you. where are client settings? In control panel or system settings?

[Vao Tsun]

neither, nor. it depends on client. but basically, check out postgresql.org/docs/current/static/… and much shorter (not sure if its a plus or minus) postgresql.org/docs/current/static/sql-set.html

[Pezhman Parsaee]

Thanks. I really appreciate you. Would you please answer my last questions that i just add now to first post now ? (Questions 5-10). Thanks a lot