Postgresql timestamp formatting
I can't remember how to format the timestamp in PG and record the commonly used formatting time
-- Year select to_char(to_timestamp(1608018517000 / 1000),'yyyy'); -- Year-moon select to_char(to_timestamp(1608018517000 / 1000),'yyyy-MM'); -- Year-moon-day select to_char(to_timestamp(1608018517000 / 1000),'yyyy-MM-dd'); -- Year-moon-day hour select to_char(to_timestamp(1608018517000 / 1000),'yyyy-MM-dd HH24'); correspondJAVA of“yyyy-MM-dd HH” -- Year-moon-day hour:point select to_char(to_timestamp(1608018517000 / 1000),'yyyy-MM-dd HH24:MI');correspondJAVA of“yyyy-MM-dd HH:mm” -- Year-moon-day hour:point:Second select to_char(to_timestamp(1608018517000 / 1000),'yyyy-MM-dd HH24:MI:SS');correspondJAVA of“yyyy-MM-dd HH:mm:ss”
Common knowledge on converting timestamp format in postgresql
Prerequisite: When the timestamp type is saved in the database, we need to use this timestamp to make optimistic data locks. Then, select it for a long time, and then determine whether the timestamp is the same as the query when updated.
The following SQL query result is "2018-08-20 10:09:10.815125", and the return type can be processed as a String. Returning to json is convenient to use.
SQL> select to_char(updateTime, 'yyyy-mm-dd hh24:mi:') from tbl_A;
When updating, the parameter is passed into the string "2018-08-20 10:09:10.815125", so it needs to be converted in SQL to match the timeStamp data type of the updateTime field.
SQL> update tbl_A set username='XXX' where userid='001' and updateTime = to_timestamp('2018-08-20 10:09:10.815125','yyyy-mm-dd hh24:mi:');Another sheet attached
| function | Return type | describe | example |
| to_char(timestamp, text) | text | Convert timestamps to strings | to_char(current_timestamp, 'HH12:MI:SS') |
| to_char(interval, text) | text | Convert time intervals into strings | to_char(interval '15h 2m 12s', 'HH24:MI:SS') |
| to_char(int, text) | text | Convert integers to strings | to_char(125, '999') |
| to_char(double precision, text) | text | Convert real/double precision numbers into strings | to_char(125.8::real, '999D9') |
| to_char(numeric, text) | text | Convert numeric to string | to_char(-125.8, '999D99S') |
| to_date(text, text) | date | Convert string to date | to_date('05 Dec 2000', 'DD Mon YYYY') |
| to_timestamp(text, text) | timestamp | Convert strings to timestamps | to_timestamp('05 Dec 2000', 'DD Mon YYYY') |
| to_timestamp(double) | timestamp | Convert UNIX epoch to timestamp | to_timestamp(200120400) |
| to_number(text, text) | numeric | Convert string to numeric | to_number('12,454.8-', '99G999D9S') |
| model | describe |
| HH | Hours of a day (01-12) |
| HH12 | Hours of a day (01-12) |
| HH24 | Hours of a day (00-23) |
| MI | Minutes (00-59) |
| SS | Seconds (00-59) |
| MS | Milliseconds (000-999) |
| US | Microseconds (000000-999999) |
| AM | Noon sign (caps) |
| Y,YYY | Year with commas (4 and more) |
| YYYY | Year (4 and more) |
| YYY | The last three of the year |
| YY | The last two of the year |
| Y | The last one of the year |
| MONTH | Full-length capital month name (blank fills with 9 characters) |
| Month | Full length mixed case month name (blank fills 9 characters) |
| month | Full-length lowercase month name (blank fills to 9 characters) |
| MON | Capital abbreviation month name (3 characters) |
| Mon | Abbreviation mixed case month name (3 characters) |
| mon | Lowercase abbreviation month name (3 characters) |
| MM | Month No. (01-12) |
| DAY | Full-length capital date name (blank fills with 9 characters) |
| Day | Full length mixed case date name (blank fills 9 characters) |
| day | Full-length lowercase date name (blank fills with 9 characters) |
| DY | Abbreviation capital date name (3 characters) |
| Dy | Abbreviation mixed case date name (3 characters) |
| dy | Abbreviation lowercase date name (3 characters) |
| DDD | Days of the year (001-366) |
| DD | Days of a month (01-31) |
| D | Days of the week (1-7; Sunday is 1) |
| W | Weeks in a month (1-5) (the first week starts from the first day of the month) |
| WW | Weeks in a year (1-53) (the first week starts from the first day of the year) |
Summarize
The above is personal experience. I hope you can give you a reference and I hope you can support me more.