Menu
I have date in postgresql in format "17/12/2011".
How can i convert it into milliseconds using select clause of postgreql ?
Currently i am just executing select clause as
select tableDate,tableSales
from table_name
I want to have something like when I select tableDate it should be converted into milliseconds using some postgresql functions.
tableDate DATE
tableSales Numeric
821
You can change the format in the postgresql. conf file. The date/time styles can be selected by the user using the SET datestyle command, the DateStyle parameter in the postgresql. conf configuration file, or the PGDATESTYLE environment variable on the server or client.
PostgreSQL uses the yyyy-mm-dd format for storing and inserting date values. If you create a table that has a DATE column and you want to use the current date as the default value for the column, you can use the CURRENT_DATE after the DEFAULT keyword. Let's look into some examples for better understanding.
Postgresql date to string yyymmdd In Postgresql, dates can be converted in specific string formats like yyymmdd, where yyyy for a year, mm for a month, and dd for the date. For that conversion, we will use the to_char function to format the date as a string. Syntax: TO_CHAR(date_value, string_format);
extract(epoch from ...) will return the number of seconds since 1970-01-01 00:00:00 so all you need to do is to multiply that by 1000:
select extract(epoch from tableDate) * 1000, tableSales
from table_name
More details in the manual:
http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT
136
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
