postgresql 拾遗

1. 从timestamp提取date和time

to extract date from coldate,

SELECT date(coldate) FROM mytable;


to extract time, doing time(coldate) will give an error instead,

SELECT "time"(coldate) FROM mytable


another way

SELECT coldate::time FROM mytable




2.string to timestamp

to_timestamp(timestring,format)


to_timestamp('15:33:33','HH24:MI:SS)

select to_timestamp('2011-12-30 00:30:00','YYYY-MM-DD HH24:MI:SS')::timestamp without time zone;

3. 字符串连接

‘string1’ || ‘string2’