So I was minding my own business trying to manually insert an entry in the user table for the XMPP server that I am using when I got an error when I tried to set the creation date. Turns out that the creation date column in the user table is a 15 character string, 0 padded, that represents the number of milliseconds since January 1st, 1970. Why it is not a standard date type is beyond me but I digress.
Well if you ever need to do such a thing, and who doesn’t, here is the SQL query to do it:
select lpad(round(extract(epoch from now())*1000),15,0) as result from dual;
result
-------------------------
001144246603582
Note that this query is specific to Postgres. You may have to alter a few things to get it to work with whatever database you are using.
It is also worth noting that extracting the epoch from the date only returns the number of seconds since January 1, 1970. To get milliseconds we need to multiply by 1000.
