syntax.us Let the syntax do the talking
Blog Contact Posts Questions Tags Hire Me

Question:
With Postgres how do I convert a date to unixtime?

Let Postgres do the talking:
SELECT
ydate
,EXTRACT(EPOCH FROM ydate)::INT AS unixtime
,closing_price
FROM mydata
WHERE ydate > '2014-12-01' 
ORDER BY ydate
;
   ydate    |  unixtime  | closing_price 
------------+------------+---------------
 2014-12-02 | 1417478400 |       2066.55
 2014-12-03 | 1417564800 |       2074.33
 2014-12-04 | 1417651200 |       2071.92
 2014-12-05 | 1417737600 |       2075.37
 2014-12-08 | 1417996800 |       2060.31
 2014-12-09 | 1418083200 |       2059.82
 2014-12-10 | 1418169600 |       2026.14
 2014-12-11 | 1418256000 |       2035.33
 2014-12-12 | 1418342400 |       2002.33
 2014-12-15 | 1418601600 |       1989.63
 2014-12-16 | 1418688000 |       1972.74
 2014-12-17 | 1418774400 |       2012.89
 2014-12-18 | 1418860800 |       2061.23
 2014-12-19 | 1418947200 |       2070.65
 2014-12-22 | 1419206400 |       2078.54
 2014-12-23 | 1419292800 |       2082.17
 2014-12-24 | 1419379200 |       2081.88
 2014-12-26 | 1419552000 |       2088.77
 2014-12-29 | 1419811200 |       2090.57
(19 rows)

dan@z2.z2 ~/spy611/script $ 
dan@z2.z2 ~/spy611/script $ 


syntax.us Let the syntax do the talking
Blog Contact Posts Questions Tags Hire Me