CODEWITHSUNDEEP
sqlpostgresql
Lina Linutina
Lina Linutina

Reputation: 443

How can I extract a weekday from a timestamp in postgresql?

I have a small table called timestamps with the only variable ts in it:

ts
1541990258796
1541990258312
1541990258367
<...>

Based on ts values of timestamps table, I want to create a factTime table where I extract hour, day and weekday from those ts values. My current query is that and it works.

time_table_insert = (
    """
    INSERT INTO factTime (start_time, hour, day_of_month, weekday)
    SELECT DISTINCT ts AS start_time,
        EXTRACT(HOUR FROM start_time) AS hour,
        EXTRACT(DAY FROM start_time) AS day_of_month,
        EXTRACT(DOW FROM start_time) AS weekday
FROM (SELECT DISTINCT ts,'1970-01-01'::date + ts/1000 * interval '1 second' as start_time FROM timestamps);
""")

Now I insert a weekday expressed in number with DOW. Is there a way to extract its actual name? How should I change my query to get the actual weekday, i.e. Monday, Friday, etc.

Upvotes: 2

Views: 1380

Answers (2)

VBoka
VBoka

Reputation: 9083

Here is how you can do it:

to_char('1970-01-01'::date + 1541990258312/1000 * interval '1 second', 'day')

I used '1970-01-01'::date + 1541990258312/1000 * interval '1 second' from your code - the way you get start_time.

Use to_char function with 'day' parameter. You can also use other day parameters:

DAY full upper case day name (blank-padded to 9 chars)

Day full capitalized day name (blank-padded to 9 chars)

day full lower case day name (blank-padded to 9 chars)

Here you can see results of all three way's: DEMO

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269553

Convert to a timestamp using date arithmetic and then use extract():

select extract(dow from '1970-01-01'::timestamp + ts * interval '1 millisecond')

Use to_char() if you actually want the abbreviation rather than a number.

Upvotes: 1

Related Questions