extract dates and times from string in Redshift

» development » extract dates and times from string in Redshift

By : ibrahim

Date : January 11 2021, 03:32 PM

may help you . I have a column like the one below. The last two sets of numbers are date and time. I want to create date-time column by extracting values from the column. , This substring extracts the date time part from your string.

code :

SELECT substring(col_name,regexp_instr(col_name,'-',1,regexp_count(col_name,'-')-1)+1,
                 regexp_instr(col_name,'.json',1)-regexp_instr(col_name,'-',1,regexp_count(col_name,'-')-1)-1)

WITH test(col_name) AS (
SELECT '1002206391240385-sponsoredProducts-SameDayPull-20190627-012313.json'::TEXT
)
SELECT col_name,
substring(col_name,regexp_instr(col_name,'-',1,regexp_count(col_name,'-')-1)+1,
                  regexp_instr(col_name,'.json',1)-regexp_instr(col_name,'-',1,regexp_count(col_name,'-')-1)-1) datetime
FROM test

col_name                                                            datetime
1002206391240385-sponsoredProducts-SameDayPull-20190627-012313.json 20190627-012313

Share :

Extract dates times from a data.frame in R

By : rgaltea

Date : March 29 2020, 07:55 AM

hope this fix your issue I have a dataset with some date time like this "{datetime:2015-07-01 09:10:00" So I wanted to remove the text, and then keep the date & the time as as.Date returns only the date. So I write this code but the only problem I have is that during the second line with strsplit, it only returns me the date time of the first line and so erase the others... I woud love to get ALL my date time not only the first. I thought about sapply maybe, but I can't make it right I have many errors or maybe with a loop for? I am novice to R so I don't really know how to do this the best way. , You could use the format argument to avoid all the strsplit:

code :

times <- as.POSIXct(data$`Date Time`, format='{datetime:%Y-%m-%d %H:%M:%S')

data$datetime <- times

data$Date <- as.Date(times)
data$Time <- strftime(times, format='%H:%M:%S')

Redshift Extract String Between Two Patterns (regexp_substr)

By : Germano Caumo

Date : March 29 2020, 07:55 AM

I wish this help you Found a hack solution that involves two levels of queries to get around having to use regexp_subtr. The inner query uses substring and position to pull out all of the text after the deviceSerialNumber tag. The outer query uses the same two functions to cut off any text after the next &

code :

select substring(pre_serial_num, 1, position('&' in pre_device_id || '&') - 1) as device_id
from
    (select substring(session_tags,position('deviceSerialNumber' in session_tags) + 20, 40) as pre_device_id 
    from table) a

Is there a way to extract all of the dates from a string in Redshift?

By : airman000616

Date : March 29 2020, 07:55 AM

it fixes the issue For a given string, is it possible to count (and maybe extract) how many dates there are in there? As an example, the string may look like (2019-06-01, 2019-06-02, 2019-06-03) or (This is the first date: 2019-06-01; This is the second date: 2019-06-02) or in some other form. In the first one, it should return 3. And it should return 2 in the second example text. , This should work

code :

select regexp_count(str_field, '[0-9]{4}-[0-9]{2}-[0-9]{2}') from your table

with test_data as (
select '2019-06-01, 2019-06-02, 2019-06-03' str_field union all
select 'This is the first date: 2019-06-01; This is the second date: 2019-06-02'
)
select regexp_count(str_field, '[0-9]{4}-[0-9]{2}-[0-9]{2}') date_count from test_data

date_count
3
2

with test_data as (select '2019-06-01, 2019-06-02, 2019-06-03' str_field union all
select 'This is the first date: 2019-06-01; This is the second date: 2019-06-02'
)
select regexp_substr(str_field, '[0-9]{4}-[0-9]{2}-[0-9]{2}',1,1) date1,
       regexp_substr(str_field, '[0-9]{4}-[0-9]{2}-[0-9]{2}',1,2) date2,
       regexp_substr(str_field, '[0-9]{4}-[0-9]{2}-[0-9]{2}',1,3) date3
  from test_data

date1       date2       date3
2019-06-01  2019-06-02  2019-06-03
2019-06-01  2019-06-02

Extract dates, times and date ranges from text in PHP

By : Ayoob Nabwani

Date : March 29 2020, 07:55 AM

To fix the issue you can do I'm building a local events calendar which takes RSS feeds and website scrapes and extracts event dates from them. , I think you can sum up the regex in your question like the one below.

Extract two combined dates and times from a string

By : Randy

Date : March 29 2020, 07:55 AM

I wish this help you Could you please let me know how I can extract date and time from ("2015-08-11 03:14:00 UTC--2015-08-11 04:14:00 UTC"). Note that this string contains a time interval with two dates and two times. I would like to break it down into 4 individual strings such as Date 1, Time 1, Date 2, Time 2 and then store them in 4 separate vectors. , Try the following.