I want to parse the time before 1970/1/1 00:00:00.

[to4kawa]

I'm trying and trying to simulate R code with Splunk.
When running R data in Splunk, data before 1970 appears.
I understand that strptime doesn't work.
But strftime works.
I wrote the brute force code.
But I think there is a better way.

What is your method?

Data:

| makeresults
| eval _raw="year  Qtr1   Qtr2    Qtr3  Qtr4
1960  160.1  129.7   84.8  120.1
1961  160.1  124.9   84.8  116.9
1962  169.7  140.9   89.7  123.3
1963  187.3  144.1   92.9  120.1
1964  176.1  147.3   89.7  123.3
1965  185.7  155.3   99.3  131.3
1966  200.1  161.7  102.5  136.1
1967  204.9  176.1  112.1  140.9
1968  227.3  195.3  115.3  142.5
1969  244.9  214.5  118.5  153.7
1970  244.9  216.1  188.9  142.5
1971  301.0  196.9  136.1  267.3
1972  317.0  230.5  152.1  336.2
1973  371.4  240.1  158.5  355.4
1974  449.9  286.6  179.3  403.4
1975  491.5  321.8  177.7  409.8
1976  593.9  329.8  176.1  483.5
1977  584.3  395.4  187.3  485.1
1978  669.2  421.0  216.1  509.1
1979  827.7  467.5  209.7  542.7
1980  840.5  414.6  217.7  670.8
1981  848.5  437.0  209.7  701.2
1982  925.3  443.4  214.5  683.6
1983  917.3  515.5  224.1  694.8
1984  989.4  477.1  233.7  730.0
1985 1087.0  534.7  281.8  787.6
1986 1163.9  613.1  347.4  782.8"
| multikv forceheader=1
| table year  Qtr1   Qtr2    Qtr3  Qtr4
| untable year Qtr UKgas
| eval time=year."-".case(Qtr="Qtr1","01-01",Qtr="Qtr2","04-01",Qtr="Qtr3","07-01",Qtr="Qtr4","10-01")
| table time UKgas
| rename time as _time
| rename COMMENT as "_time is string, not epoch time. I want to parse it to epoch"

my query:

| makeresults
| eval _raw="year  Qtr1   Qtr2    Qtr3  Qtr4
1960  160.1  129.7   84.8  120.1
1961  160.1  124.9   84.8  116.9
1962  169.7  140.9   89.7  123.3
1963  187.3  144.1   92.9  120.1
1964  176.1  147.3   89.7  123.3
1965  185.7  155.3   99.3  131.3
1966  200.1  161.7  102.5  136.1
1967  204.9  176.1  112.1  140.9
1968  227.3  195.3  115.3  142.5
1969  244.9  214.5  118.5  153.7
1970  244.9  216.1  188.9  142.5
1971  301.0  196.9  136.1  267.3
1972  317.0  230.5  152.1  336.2
1973  371.4  240.1  158.5  355.4
1974  449.9  286.6  179.3  403.4
1975  491.5  321.8  177.7  409.8
1976  593.9  329.8  176.1  483.5
1977  584.3  395.4  187.3  485.1
1978  669.2  421.0  216.1  509.1
1979  827.7  467.5  209.7  542.7
1980  840.5  414.6  217.7  670.8
1981  848.5  437.0  209.7  701.2
1982  925.3  443.4  214.5  683.6
1983  917.3  515.5  224.1  694.8
1984  989.4  477.1  233.7  730.0
1985 1087.0  534.7  281.8  787.6
1986 1163.9  613.1  347.4  782.8"
| multikv forceheader=1
  | table year  Qtr1   Qtr2    Qtr3  Qtr4
  | untable year Qtr UKgas
  | eval time=(year+20)."-".case(Qtr="Qtr1","01-01",Qtr="Qtr2","04-01",Qtr="Qtr3","07-01",Qtr="Qtr4","10-01")
  | table time UKgas
  | eval tmp_time=strptime(time,"%F")
  | eval tmp_time=tmp_time-(365*24*60*60*20)
  | eval tmp_time_p=strftime(tmp_time,"%F")
  | eval days=substr(tmp_time_p,9,10)
  | eval tmp_time=tmp_time-(days-1)*(24*60*60)
  | eval time=strftime(tmp_time,"%F")
  | table time UKgas

[to4kawa · Accepted solution]

Sample code:

| makeresults 
| eval time=0 
| fieldformat _time=strftime(_time,"%c %::z") 
| appendpipe 
    [| eval time=-62167252739 
    | eval time_prefix=9*60*60+18*60+59] 
| eval _time = time

Result:

_time                                time            time_prefix
Thu Jan  1 09:00:00 1970 +09:00:00  0    
Sat Jan  1 00:00:00 0000 +09:18:59  -62167252739    33539

strptime automatically recognizes the time zone. so, this result.

If we just make a table, we don't need to change the time strings to UNIX time.
When measuring or predicting time differences, conversion is required.

As I did, it seems that we have to change it to the dates that can be converted with strptime and then modify it later.

[niketn]

@to4kawa can you add some sample data, do you have control over the date format in the data you are indexing to Splunk?

[vnravikumar]

Hi

Here i got few info.

UNIX data represents different points in time as signed integers, traditionally of 32 bits, by encoding the UNIX timestamp. Because it uses 32 bits, UNIX time can only cover approximately 136 years in total. In other words, the maximum representable time for UNIX time is 19 January 2038 and the minimum representable time is 13 December 1901.

Date & timeTimes that occurred before 1970 (the epoch time) have a negative value. When the counter passes the maximum date, the counter will ‘run out’ and time will ‘wrap around’ and be stored as a negative number (13 December 1901). This is referred to as the UNIX millennium bug.

[to4kawa]

Thanks @vnravikumar
I see.
though I can parse until 1901/12/13, isn't it?
But before 1970, it could not be parsed.

Is there a good way?