How to convert timestamp to date & time I am currently working on bigquery(standard sql), where I have a timestamp field which I want to convert to date and time in 2 separate columns. I tried doing this select EXTRACT(DATE FROM timestamp) from table . 

I tried casting timestamp to date as well, but it is giving me an error. Any suggestions would be really appreciated

Input

timestamp             
1539167364779001       
1542242226310005
1544881556726002

Expected output

date         time
10/10/2018   3:59:24 PM 
11/15/2018   6:07:06 AM
12/15/2018   7:15:56 PM
On the MySQL side...

mysql&gt; SELECT FROM_UNIXTIME(1539167364779001 * 1e-6);
+----------------------------------------+
| FROM_UNIXTIME(1539167364779001 * 1e-6) |
+----------------------------------------+
| 2018-10-10 03:29:24.779001             |
+----------------------------------------+
1 row in set (0.00 sec)


Notes:


Having a TS in microseconds is rather unusual; where is it coming from?
I multiplied by one-millionth instead of dividing by a million because the arithmetic rounds division to some number of decimal places.
Are there timezone issues?
That value should nicely slip into a column declared TIMESTAMP(6) or DATETIME(6).
To remove the leading zero from the hour and/or strip off the fractional sections, use the function DATE_FORMAT().  (Oh, was there an implied am/pm?)  (Oh, you wanted truncation?)