Menu
I have a column that requires a timestamp with time zone but I cannot use a built in pgSQL function nor can I modify the column to accept null dates. How can I insert a string date in the right format manually that represents no time?
967
The TO_DATE function in PostgreSQL is used to converting strings into dates. Its syntax is TO_DATE(text, text) and the return type is date. The TO_TIMESTAMP function converts string data into timestamps with timezone. Its syntax is to_timestamp(text, text) .
Handling empty strings in PostgreSQL In Oracle, because empty strings are treated as NULL, the preceding insert statements #2 and #3 will store NULL for column tname in the table. However, in PostgreSQL, the table will store NULL for the #2 and an empty string for the #3 insert statements.
The DATE and TIMESTAMP functions do exactly what you are looking for. If you have a STRING column where its format is like TIMESTAMP , you can simply apply it. Then, DATE will extract just the date and it takes care of the NULL values.
The PostgreSQL CURRENT_DATE function returns the current date (the system date on the machine running PostgreSQL) as a value in the 'YYYY-MM-DD' format. In this format, 'YYYY' is a 4-digit year, 'MM' is a 2-digit month, and 'DD' is a 2-digit day. The returned value is a date data type.
There is no empty timestamp other than null, but you can use one of the special date/time values infinity or -infinity.
select 'infinity'::timestamp, '-infinity'::timestamp
timestamp | timestamp
-----------+-----------
infinity | -infinity
(1 row)
See Table 8.13. Special Date/Time Inputs.
92
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
