In Postgres, how would I convert the following text to a date?

In the following expression

"DATETIME"::date || ' 23:59:59.000'

I'm converting a DATETIME field to a pure date. The string concatenation changes the value to the very end of the day. I would now like to insert this value back into another DATETIME field but I am unsur开发者_JS百科e how to convert from text back to DATETIME

This expression should work.

("DATETIME"::date || ' 23:59:59.000')::timestamp

Depending on your application, you might need to consider leap seconds. The 59th second isn't invariably the last second before the next date.

I assume the type of the DATETIME field is something like TIMESTAMP. If so, just cast the whole string back to a TIMESTAMP:

("DATETIME"::date || ' 23:59:59.00')::timestamp